sql查询语句
-1
所有回答(6)
0
SELECT NAME,CHECK,COUNT(1) AS TOTAL,SUM(CASE WHEN EVALUATE = '优' THEN 1 ELSE 0 END) AS 优,SUM(CASE WHEN EVALUATE = '良' THEN 1 ELSE 0 END) AS 良,SUM(CASE WHEN EVALUATE = '差' THEN 1 ELSE 0 END) AS 差
FROM TABLENAME
GROUP BY NAME,CHECK
0
select name as '姓名',[check] as '考核项',COUNT(1) as '考核总数',
SUM(case when elvaluate='优' then 1 else 0 end) as '优',
SUM(case when elvaluate='良' then 1 else 0 end) as '良',
SUM(case when elvaluate='差' then 1 else 0 end) as '差'
from tablename group by name,[check] order by name desc
其实一楼已写出答案。
-1
SELECT NAME,[CHECK],COUNT(1) AS TOTAL,
SUM(CASE WHEN EVALUATE = '优' THEN 1 ELSE 0 END) AS 优,
SUM(CASE WHEN EVALUATE = '良' THEN 1 ELSE 0 END) AS 良,
SUM(CASE WHEN EVALUATE = '差' THEN 1 ELSE 0 END) AS 差
FROM [stuinfo]
GROUP BY NAME,[CHECK]
order by name desc
sql05中的优良差的记录不显示
0
2005可以显示啊。case when有问题的话，你试下字段嵌套子查询能不能显示：
select name,check,
(select count(evaluate) from tb where name = a.name and check = a.check and evaluate = '优') as 优,
(select count(evaluate) from tb where name = a.name and check = a.check and evaluate = '良') as 良,
(select count(evaluate) from tb where name = a.name and check = a.check and evaluate = '差') as 差
from tb a
group by name,check
漏了个考核次数
select name,check,count(1) as 考核次数,
(select count(evaluate) from tb where name = a.name and check = a.check and evaluate = '优') as 优,
(select count(evaluate) from tb where name = a.name and check = a.check and evaluate = '良') as 良,
(select count(evaluate) from tb where name = a.name and check = a.check and evaluate = '差') as 差
from tb a
group by name,check
@天空10047: 用select 子查询都显示不出来？
@fhyf:看图片
@天空10047: 建的数据有多余空格？改成 like ''%优%' 看看
0
0分这么多人帮你解决还磨磨唧唧的，牛人。
跟05,08,12有毛线关系。
1楼解答是正确的，你自己看清楚自己数据
@天空10047: 自己数据问题扯什么实践检验。
0
我实践过了一楼的是正解啊。但是灵活性不是很大
