|
/*用CASE WHEN实现sqlserver数据库的分类汇总功能*/
/*例1：统计个数*/
/*表结构
id? cityid? major
1?? 1??????? ?A
2?? 1??????? ?B
3?? 2??????? ?B
4?? 2??????? ?A
5?? 1??????? ?A
6?? 2? ????? B
7?? 1??????? C
8?? 2??????? ?C
结果：
cityId? A? B? C
1??????? 2? 1? 1
2??????? 1? 2? 1
*/
SELECT cityId,SUM(CASE WHEN major='A' THEN 1 ELSE 0 END) AS 'A',
????????????????????? SUM(CASE WHEN major='B' THEN 1 ELSE 0 END) AS 'B',
???????????????????? ?SUM(CASE WHEN major='C' THEN 1 ELSE 0 END) AS 'C'
?????????? ?FROM TestCase1 GROUP BY cityId
/*例1：统计总和数*/
/*表结构
Id?cityId?catalogId?Num
1?? 1??????? A???????????? ?3
2?? 1??????? B???????????? ?5
3?? 2??????? A???????????? ?8
4?? 2??????? B??????????? ?2
5?? 1??????? A???????????? 1
6?? 1??????? B??????????? ?2
7?? 2??????? C??????????? 3
8?? 2??????? C??????????? 4
9?? 1??????? C??????????? 1
10?? 1???????C???????? ? 2
11?? 2?????? A?????????? 3
12?? 2?????? B???????? ??4
?
结果：
cityId? A????? B???? C?? 总计
1??????? 4???? ?7???? 3? 14
2????? ?11??? ?6???? 7? 24
*/
SELECT cityId,SUM(CASE WHEN catalog='A' THEN num ELSE 0 END) AS 'A',?????????????????????? ?SUM(CASE WHEN catalog='B' THEN num ELSE 0 END) AS 'B',????????????????????? ?SUM(CASE WHEN catalog='C' THEN num ELSE 0 END) AS 'C',?????????????????????? SUM(Num) as '总计'??????????? ?????? FROM TestCase2 GROUP BY cityId
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
