首页 新闻 赞助 找找看

sql查询语句

-1
[待解决问题]

如何编写sql语句????主要是优良差的记录显示个数

恋人星空的主页 恋人星空 | 菜鸟二级 | 园豆:260
提问于:2014-07-24 16:42
< >
分享
所有回答(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

圈圈点点 | 园豆:212 (菜鸟二级) | 2014-07-24 16:55
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

 

其实一楼已写出答案。

会飞的金鱼 | 园豆:881 (小虾三级) | 2014-07-24 19:47
-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中的优良差的记录不显示

恋人星空 | 园豆:260 (菜鸟二级) | 2014-07-24 20:23
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

fhyf | 园豆:204 (菜鸟二级) | 2014-07-28 16:25

漏了个考核次数

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

支持(0) 反对(0) fhyf | 园豆:204 (菜鸟二级) | 2014-07-28 16:30

@天空10047: 用select 子查询都显示不出来?

支持(0) 反对(0) fhyf | 园豆:204 (菜鸟二级) | 2014-07-29 19:34

@fhyf:看图片 

支持(0) 反对(0) 恋人星空 | 园豆:260 (菜鸟二级) | 2014-07-29 19:51

@天空10047: 建的数据有多余空格?改成 like ''%优%' 看看

支持(0) 反对(0) fhyf | 园豆:204 (菜鸟二级) | 2014-07-29 20:43
0

0分这么多人帮你解决还磨磨唧唧的,牛人。

跟05,08,12有毛线关系。

1楼解答是正确的,你自己看清楚自己数据

+小马哥++ | 园豆:906 (小虾三级) | 2014-08-04 17:06

@天空10047: 自己数据问题扯什么实践检验。

支持(1) 反对(0) +小马哥++ | 园豆:906 (小虾三级) | 2014-08-05 08:02
0

我实践过了一楼的是正解啊。但是灵活性不是很大

我是糖糖 | 园豆:446 (菜鸟二级) | 2014-10-19 20:18
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册