SELECT a.deptid AS 部门ID, b.dept AS 部门,
SUM(CASE WHEN a.sex = '1' THEN 1 ELSE 0 END) AS '男生人数',
SUM(CASE WHEN a.sex = '0' THEN 1 ELSE 0 END) AS '女生人数'
FROM @worker a
INNER JOIN
@dept b ON a.deptid = b.id
GROUP BY a.deptid ,b.dept
SUM(CASE WHEN a.sex = '1' THEN 1 ELSE 0 END) AS '男生人数',
SUM(CASE WHEN a.sex = '0' THEN 1 ELSE 0 END) AS '女生人数'
FROM @worker a
INNER JOIN
@dept b ON a.deptid = b.id
GROUP BY a.deptid ,b.dept
SUM(CASE WHEN a.sex = '1' THEN 1 ELSE 0 END) AS '男生人数',
SUM(CASE WHEN a.sex = '0' THEN 1 ELSE 0 END) AS '女生人数'
FROM worker a
INNER JOIN
dept b ON a.deptid = b.id
GROUP BY a.deptid ,b.dept
, b.dept AS 部门
from ( a.deptid AS 部门ID
,SUM(CASE WHEN a.sex = '1' THEN 1 ELSE 0 END) AS '男生人数',
,SUM(CASE WHEN a.sex = '0' THEN 1 ELSE 0 END) AS '女生人数'
FROM worker AS a
GROUP BY a.deptid
)T
INNER JOIN dept AS b ON T.deptid = b.id