Question

sql 分类汇总查询语句

比如:

id dept pay zt
———————————–
1 办公室 20.00 01
2 局领导 10.00 02
3 办公室 40.00 03
4 局领导 10.00 01
5 办公室 50.00 01
6 局领导 10.00 02
7 办公室 20.00 02
8 局领导 10.00 02
……
我现在想统计01、02两种状态的数量和pay合计。所有状态的数量
显示显示效果如下:
序号 部门 合计 个数（01状态） 个数（02状态） 总数
————————————————————————————————
1 办公室 90.00 2 1 4
2 局领导 50.00 1 3 4

Answer 1

SELECT A.DEPT,A.PAY,C.[01],C.[02],B.ZTNUM
FROM
(
SELECT DEPT,SUM(PAY) AS PAY FROM TABLE_1 WHERE ZT IN(01,02) GROUP BY DEPT
)A
LEFT JOIN
(
SELECT DEPT,COUNT(ZT) AS ZTNUM FROM TABLE_1 GROUP BY DEPT
) B
ON A.DEPT=B.DEPT
LEFT JOIN
(
SELECT *
FROM
(SELECT DEPT,ZT,COUNT(ZT) AS ZTNUM FROM TABLE_1 WHERE ZT IN(01,02) GROUP BY DEPT,ZT)A
PIVOT
(
SUM(A.ZTNUM)
FOR A.ZT IN ([01],[02])
) AS TPIVOT
) C
ON B.DEPT=C.DEPT

Answer 2

select dept as 部门,sum(pay) as 合计,sum(case zt when '01'then 1 else 0 end) as 个数（01状态）,
sum(case zt when '02'then 1 else 0 end) as 个数（02状态）,count(*) as 总数
from 表 group by dept

以上，希望对你有所帮助