DEPTNO PRESIDENT MANAGER CLERK SALESMAN ANALYST
10 1 1 1 0 0
20 0 1 2 0 2
30 0 1 1 4 0
按部门分组再做个行列转换就行，代码如下
SELECT DEPTNO,SUM(CASE WHEN JOB='PRESIDENT' THEN 1 ELSE 0 END) PRESIDENT,
SUM(CASE WHEN JOB='MANAGER' THEN 1 ELSE 0 END) MANAGER,
SUM(CASE WHEN JOB='CLERK' THEN 1 ELSE 0 END) CLERK,
SUM(CASE WHEN JOB='SALESMAN' THEN 1 ELSE 0 END) SALESMAN,
SUM(CASE WHEN JOB='ANALYST' THEN 1 ELSE 0 END) ANALYST
FROM EMP GROUP BY DEPTNO;
你这表本来不就是各部门的职位人数吗，还统计个鬼呀
原数据呢