WITH t AS
( SELECT 'd001' AS deptcode,'大专' AS degreecode FROM dual
UNION ALL
SELECT 'd001','本科' FROM dual
UNION ALL
SELECT 'd002','本科' FROM dual
UNION ALL
SELECT 'd002','本科' FROM dual
UNION ALL
SELECT 'd003','研究生' FROM dual
UNION ALL
SELECT 'd004','博士' FROM dual
)
SELECT deptcode,
"大专",
"大专"/cnt * 100
|| '%',
"本科",
"本科"/cnt * 100
|| '%',
"本科以上",
"本科以上"/cnt * 100
|| '%'
FROM
(SELECT deptCode,
SUM(DECODE(degreecode,'大专',1,0)) AS "大专",
SUM(DECODE(degreecode,'本科',1,0)) AS "本科",
SUM(DECODE(degreecode,'大专',0,'本科',0,1)) AS "本科以上",
COUNT(1) AS cnt
FROM t
GROUP BY deptcode
)