对一个表的多个列进行统计

最新推荐文章于 2021-07-18 08:50:54 发布

Air_cz

最新推荐文章于 2021-07-18 08:50:54 发布

阅读量390

点赞数

文章标签： sqlserver sql

本文链接：https://blog.csdn.net/Air_cz/article/details/109093358

版权

SQL 数据统计横表转换 CASE语句聚合函数

关键词由CSDN通过智能技术生成

统计多列数据到一张横表中

select t.*,b.Name as 'MC',SUBSTRING(t.DM,1,2) as SZ from (SELECT XQDH as 'DM',
 SUM(CASE WHEN KM1='语文'and KLMC='高起文' THEN 1 ELSE 0 END) as 'WKYW',
 SUM(CASE WHEN KM2='数学' and KLMC='高起文' THEN 1 ELSE 0 END) as 'WKSX',
 SUM(CASE WHEN KM3='英语' and KLMC='高起文' THEN 1 ELSE 0 END) as 'WKYY',
 SUM(CASE WHEN KM4='史地综合' THEN 1 ELSE 0 END) as 'SDZH',
 SUM(CASE WHEN KM1='语文'and KLMC='高起理' THEN 1 ELSE 0 END) as 'LKYW',
 SUM(CASE WHEN KM2='数学' and KLMC='高起理' THEN 1 ELSE 0 END) as 'LKSX',
 SUM(CASE WHEN KM3='英语' and KLMC='高起理' THEN 1 ELSE 0 END) as 'LKYY',
 SUM(CASE WHEN KM4='理化综合' THEN 1 ELSE 0 END) as 'LHZH',
 SUM(CASE WHEN YSWY=2  THEN 1 ELSE 0 END) as 'EY',
 SUM(CASE WHEN YSWY=3 THEN 1 ELSE 0 END) as 'RY'
 FROM [BaseData].[ExamInfos]  GROUP BY XQDH)t,Code.Counties b
 where b.No = t.DM
 order by DM