Group by高级用法

发表于 | 分类于 |
字数统计: 1k | 阅读时长 ≈ 5

1.Group By Grouping Sets

Group by分组函数的自定义，与group by配合使用可更加灵活的对结果集进行分组，Grouping sets会对各个层级进行汇总，然后将各个层级的汇总值union all在一起，但却比单纯的group by + union all 效率要高

** 1 创建数据**

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE employee
(
  name          NVARCHAR2(10),
  gender        NCHAR(1),
  country       NVARCHAR2(10),
  department    NVARCHAR2(10),
  salary        NUMBER(10)
);
INSERT INTO employee VALUES ('张三','男','中国','市场部',4000);
INSERT INTO employee VALUES ('李四','男','中国','市场部',5000);
INSERT INTO employee VALUES ('王五','女','美国','市场部',3000);  
INSERT INTO employee VALUES ('赵红','男','中国','技术部',2000);
INSERT INTO employee VALUES ('李白','女','中国','技术部',5000);  
INSERT INTO employee VALUES ('王蓝','男','美国','技术部',4000);
commit;

img

2 实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SELECT country, null department, round(avg(salary), 2) FROM employee GROUP BY country
UNION ALL
SELECT null country, department, round(avg(salary), 2) FROM employee GROUP BY department;
等价于
SELECT country, department, round(avg(salary), 2) FROM employee GROUP BY GROUPING SETS (country, department);


GROUP BY GROUPING SETS (A,B,C)  等价与  GROUP BY A  
                                        UNION ALL  
                                        GROUP BY B  
                                        UNION ALL  
                                        GROUP BY C
                                        
                                        
GROUP BY GROUPING SETS ((A,B,C))  等价与  GROUP BY A,B,C  
   
   
GROUP BY GROUPING SETS (A,(B,C))  等价与  GROUP BY A  
                                          UNION ALL  
                                          GROUP BY B,C

                                        
GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B,C  
        ,GROUPING SETS (B)  
        ,GROUPING SETS (C)  
   
   
GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B,C  
        ,GROUPING SETS ((B,C))   
   
   
GROUP BY GROUPING SETS (A)  等价于  GROUP BY A,B  
        ,GROUPING SETS (B,C)        UNION ALL  
                                    GROUP BY A,C
                                    
                                    
GROUP BY A                     等价于  GROUP BY A  
        ,B                                     ,B  
        ,GROUPING SETS ((B,C))                 ,C  
   
   
GROUP BY A                    等价于  GROUP BY A,B,C  
        ,B                            UNION ALL  
        ,GROUPING SETS (B,C)          GROUP BY A,B  
   
   
GROUP BY A                    等价于 GROUP BY A,B,C  
        ,B                           UNION ALL  
        ,C                           GROUP BY A,B,C  
        ,GROUPING SETS (B,C)

2.WITH ROLLUP

在group分组字段的基础上再进行统计数据。

例子：首先在name字段上进行分组，然后在分组的基础上进行某些字段统计，表结构如下：

1
2
3
4
5
6
7
8
CREATE TABLE `test` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(25) DEFAULT NULL COMMENT '标题',
  `uid` int(11) DEFAULT NULL COMMENT 'uid',
  `money` decimal(2,0) DEFAULT '0',
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

存几条数据看看：

1
2
3
4
5
6
7
8
9
10
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('2', '国庆节', '2', '12', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('3', '这次是8天假哦', '3', '33', '老顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('4', '这是Uid=1的第一条数据哦', '1', '70', '欧阳锋');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('5', '灵白山少主', '4', '99', '欧阳克');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('7', '九阴真经创始人', '3', '12', '小顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('8', '双手互博', '2', '56', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('9', '销魂掌', '2', '19', '周伯通');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('10', '蛤蟆功', '1', '57', '欧阳锋');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('11', '绝杀掌', '3', '800', '小顽童');
INSERT INTO `test`.`test` (`Id`, `title`, `uid`, `money`, `name`) VALUES ('12', '九阴真经', '3', '84', '老顽童');

分组统计：

1
SELECT name, SUM(money) as money FROM  test GROUP BY name WITH ROLLUP;

可以看到按照name分组后对money求和统计了。上面看到 null 1242, 如何搞个别名字段比如 总金额：1242呢？也可以滴，咱们继续：

1
2
3
4
5
6
7
8
9
10
11
12
13
coalesce(a,b,c);
参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

SELECT
    sum(sale_amount - payment_amount) AS creditAmount, SUM(goods_amount),
    coalesce(buyer_name, '总金额') as buyerName
FROM
    dk_sale_order_head
WHERE
    stall_id = 10065
and buyer_name in ('tcc','QTL')
GROUP BY
    buyer_name WITH ROLLUP;