decode、sign、case在统计中的用法：

最新推荐文章于 2023-05-10 07:00:00 发布

weixin_30565199

最新推荐文章于 2023-05-10 07:00:00 发布

阅读量201

点赞数

原文链接：http://www.cnblogs.com/caroline/archive/2012/11/11/2765074.html

版权

参考网址：http://www.skillbuilders.com/download/download-sample.cfm?course=adv-sql-queries&file=Adv%20SQL%20Queries%20for%20Oracle%2002%20Decode%20and%20CASE%20Notes.pdf

说明：

在统计中用到的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               1
10              ELSE
11               0
12            END) "1000-2000",
13        SUM(CASE
14              WHEN t.sal >= 2000 AND t.sal < 3000 THEN
15               1
16              ELSE
17               0
18            END) "2000-3000",
19        
20        SUM(CASE
21              WHEN t.sal >= 3000 AND t.sal < 4000 THEN
22               1
23              ELSE
24               0
25            END) "3000-4000",
26        SUM(CASE
27              WHEN t.sal >= 4000 THEN
28               1
29              ELSE
30               0
31            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                     ename
10                  END) "小于1000的具体人",
11        SUM(CASE
12              WHEN t.sal >= 1000 AND t.sal < 2000 THEN
13               1
14              ELSE
15               0
16            END) "1000-2000",
17        wm_concat(CASE
18                    WHEN t.sal >= 1000 AND t.sal < 2000 THEN
19                     ename
20                  END) "1000-2000的具体人",
21        SUM(CASE
22              WHEN t.sal >= 2000 AND t.sal < 3000 THEN
23               1
24              ELSE
25               0
26            END) "2000-3000",
27        wm_concat(CASE
28                    WHEN t.sal >= 2000 AND t.sal < 3000 THEN
29                     ename
30                  END) "2000-3000的具体人",
31        
32        SUM(CASE
33              WHEN t.sal >= 3000 AND t.sal < 4000 THEN
34               1
35              ELSE
36               0
37            END) "3000-4000",
38        wm_concat(CASE
39                    WHEN t.sal >= 3000 AND t.sal < 4000 THEN
40                     ename
41                  END) "3000-4000的具体人",
42        SUM(CASE
43              WHEN t.sal >= 4000 THEN
44               1
45              ELSE
46               0
47            END) "大于4000",
48        wm_concat(CASE
49                    WHEN t.sal >= 4000 THEN
50                     ename
51                  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 THEN
11           '大于或等于4000'
12        END "工资分布",
13        COUNT(1) 数量
14   FROM emp t
15  GROUP BY CASE
16             WHEN t.sal < 1000 THEN
17              '大于2000'
18             WHEN t.sal >= 1000 AND t.sal < 2000 THEN
19              '1000-2000'
20             WHEN t.sal >= 2000 AND t.sal < 3000 THEN
21              '2000-3000'
22             WHEN t.sal >= 3000 AND t.sal < 4000 THEN
23              '3000-4000'
24             WHEN t.sal >= 4000 THEN
25              '大于或等于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(CASE
10                WHEN t.sal >= 1000 AND t.sal < 2000 THEN
11                 1
12                ELSE
13                 0
14              END) b,
15          SUM(CASE
16                WHEN t.sal >= 2000 AND t.sal < 3000 THEN
17                 1
18                ELSE
19                 0
20              END) c,
21          
22          SUM(CASE
23                WHEN t.sal >= 3000 AND t.sal < 4000 THEN
24                 1
25                ELSE
26                 0
27              END) d,
28          SUM(CASE
29                WHEN t.sal >= 4000 THEN
30                 1
31                ELSE
32                 0
33              END) e
34     FROM emp t)
35 SELECT '小于1000',
36        t.a
37   FROM t
38 UNION ALL
39 SELECT '1000-2000',
40        t.b
41   FROM t
42 UNION ALL
43 SELECT '2000-3000',
44        t.c
45   FROM t
46 UNION ALL
47 SELECT '3000-4000',
48        t.d
49   FROM t
50 UNION ALL
51 SELECT '大于4000',
52        t.e
53   FROM t