参考网址：
说明：
在统计中用到的MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。用sign和decode来完成比较字段大小来区某个字段
1 select decode(sign(字段1-字段2),-1,字段3,字段4) from dual;
1、使用case进行直方图的统计：
统计工资在各个区间的人数：
1 SELECT SUM(CASE 2 WHEN t.sal < 1000 THEN 3 1 4 ELSE 5 0 6 END) "小于1000", 7 SUM(CASE 8 WHEN t.sal >= 1000 AND t.sal < 2000 THEN 9 110 ELSE11 012 END) "1000-2000",13 SUM(CASE14 WHEN t.sal >= 2000 AND t.sal < 3000 THEN15 116 ELSE17 018 END) "2000-3000",19 20 SUM(CASE21 WHEN t.sal >= 3000 AND t.sal < 4000 THEN22 123 ELSE24 025 END) "3000-4000",26 SUM(CASE27 WHEN t.sal >= 4000 THEN28 129 ELSE30 031 END) "大于4000"32 FROM emp t
结果：
统计在这个区间具体的人：
1 SELECT SUM(CASE 2 WHEN t.sal < 1000 THEN 3 1 4 ELSE 5 0 6 END) "小于1000", 7 wm_concat(CASE 8 WHEN t.sal < 1000 THEN 9 ename10 END) "小于1000的具体人",11 SUM(CASE12 WHEN t.sal >= 1000 AND t.sal < 2000 THEN13 114 ELSE15 016 END) "1000-2000",17 wm_concat(CASE18 WHEN t.sal >= 1000 AND t.sal < 2000 THEN19 ename20 END) "1000-2000的具体人",21 SUM(CASE22 WHEN t.sal >= 2000 AND t.sal < 3000 THEN23 124 ELSE25 026 END) "2000-3000",27 wm_concat(CASE28 WHEN t.sal >= 2000 AND t.sal < 3000 THEN29 ename30 END) "2000-3000的具体人",31 32 SUM(CASE33 WHEN t.sal >= 3000 AND t.sal < 4000 THEN34 135 ELSE36 037 END) "3000-4000",38 wm_concat(CASE39 WHEN t.sal >= 3000 AND t.sal < 4000 THEN40 ename41 END) "3000-4000的具体人",42 SUM(CASE43 WHEN t.sal >= 4000 THEN44 145 ELSE46 047 END) "大于4000",48 wm_concat(CASE49 WHEN t.sal >= 4000 THEN50 ename51 END) "大于4000的具体人"52 FROM emp t
结果：
统计成行列格式：
1 SELECT CASE 2 WHEN t.sal < 1000 THEN 3 '大于2000' 4 WHEN t.sal >= 1000 AND t.sal < 2000 THEN 5 '1000-2000' 6 WHEN t.sal >= 2000 AND t.sal < 3000 THEN 7 '2000-3000' 8 WHEN t.sal >= 3000 AND t.sal < 4000 THEN 9 '3000-4000'10 WHEN t.sal >= 4000 THEN11 '大于或等于4000'12 END "工资分布",13 COUNT(1) 数量14 FROM emp t15 GROUP BY CASE16 WHEN t.sal < 1000 THEN17 '大于2000'18 WHEN t.sal >= 1000 AND t.sal < 2000 THEN19 '1000-2000'20 WHEN t.sal >= 2000 AND t.sal < 3000 THEN21 '2000-3000'22 WHEN t.sal >= 3000 AND t.sal < 4000 THEN23 '3000-4000'24 WHEN t.sal >= 4000 THEN25 '大于或等于4000'26 END
也可以将第一组的case进行列转行得到如下的结果：
1 WITH t AS 2 (SELECT SUM(CASE 3 WHEN t.sal < 1000 THEN 4 1 5 ELSE 6 0 7 END) a, 8 9 SUM(CASE10 WHEN t.sal >= 1000 AND t.sal < 2000 THEN11 112 ELSE13 014 END) b,15 SUM(CASE16 WHEN t.sal >= 2000 AND t.sal < 3000 THEN17 118 ELSE19 020 END) c,21 22 SUM(CASE23 WHEN t.sal >= 3000 AND t.sal < 4000 THEN24 125 ELSE26 027 END) d,28 SUM(CASE29 WHEN t.sal >= 4000 THEN30 131 ELSE32 033 END) e34 FROM emp t)35 SELECT '小于1000',36 t.a37 FROM t38 UNION ALL39 SELECT '1000-2000',40 t.b41 FROM t42 UNION ALL43 SELECT '2000-3000',44 t.c45 FROM t46 UNION ALL47 SELECT '3000-4000',48 t.d49 FROM t50 UNION ALL51 SELECT '大于4000',52 t.e53 FROM t
也可以进行列转行：
使用SIGN进行统计：
SIGN函数的说明：sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1