mysql rollup order共用_mysql5-7与mysql8-0关于with-rollup-order-by处理上的区别

title: mysql5.7与mysql8.0关于with rollup order by处理上的区别

date: 2019-10-23 16:39:09

tags:

需求：

常规问题，group by之后根据指定字段进行排序，如下根据c字段排序。

eg：select a,b,count(*) from tb_1 group by a,b with rollup order by c;

问题：

Q1：5.7就不支持with rollup 与 order by(distinct)一起用，这么优秀的功能，为啥不支持呢？

因为内部数据排序实在聚合计算之前，最后聚合列是直接被加在最后面的。并且5.7group by默认是排序的。如果从一开始就已经拍好顺序了，那后面也就不会再排序或者去重，那自然也就无法支持自定义order by或者distinct操作了。

Q2：8.0.12开始支持，那8.0.12为啥能支持呢？以及最后出现的GROUPING函数是干啥的？

Q3：用一个例子说明支持后的使用效果吧

表结构如下：

root@test05:05:10>select * from t1;

+------+------+------+

| a | b | c |

+------+------+------+

| 111 | 11 | 11 |

| 222 | 22 | 22 |

| 111 | 12 | 12 |

| 222 | 23 | 23 |

+------+------+------+

5.7支持group by字段指定排序，而8.0是不支持的

5.7

root@test05:11:22>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a ASC , b DESC WITH ROLLUP;

+------+------+------+

| a | b | SUM |

+------+------+------+

| 111 | 12 | 12 |

| 111 | 11 | 11 |

| 111 | NULL | 23 |

| 222 | 23 | 23 |

| 222 | 22 | 22 |

| 222 | NULL | 45 |

| NULL | NULL | 68 |

+------+------+------+

7 rows in set (0.00 sec)

8.0

root@test05:13:09>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a ASC , b DESC WITH ROLLUP;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ASC , b DESC WITH ROLLUP' at line 1

5.7不支持order by，而8.0开始支持order by

5.7

root@test05:13:04>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY a,b;

ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

8.0

root@test05:13:13>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY a,b;

+------+------+------+

| a | b | SUM |

+------+------+------+

| NULL | NULL | 68 |

| 111 | NULL | 23 |

| 111 | 11 | 11 |

| 111 | 12 | 12 |

| 222 | NULL | 45 |

| 222 | 22 | 22 |

| 222 | 23 | 23 |

+------+------+------+

7 rows in set (0.00 sec)

8.0支持order by任意列

5.7

root@test05:18:33>SELECT a, b, c, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY c desc;

ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY

8.0

root@test05:18:38>SELECT a, b, c, SUM(c) as SUM FROM t1 GROUP BY a, b WITH ROLLUP ORDER BY c;

+------+------+------+------+

| a | b | c | SUM |

+------+------+------+------+

| 111 | 11 | 11 | 11 |

| 111 | 12 | 12 | 12 |

| 111 | NULL | 12 | 23 |

| 222 | 22 | 22 | 22 |

| 222 | 23 | 23 | 23 |

| 222 | NULL | 23 | 45 |

| NULL | NULL | 23 | 68 |

+------+------+------+------+

8.0的GROUPING函数可以实现和5.7同样的效果

5.7

root@test05:20:01>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;

+------+------+------+

| a | b | SUM |

+------+------+------+

| 111 | 11 | 11 |

| 111 | 12 | 12 |

| 111 | NULL | 23 |

| 222 | 22 | 22 |

| 222 | 23 | 23 |

| 222 | NULL | 45 |

| NULL | NULL | 68 |

+------+------+------+

7 rows in set (0.00 sec)

8.0

root@test05:19:38>SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a ,b WITH ROLLUP ORDER BY GROUPING(a), a, GROUPING(b), b;

+------+------+------+

| a | b | SUM |

+------+------+------+

| 111 | 11 | 11 |

| 111 | 12 | 12 |

| 111 | NULL | 23 |

| 222 | 22 | 22 |

| 222 | 23 | 23 |

| 222 | NULL | 45 |

| NULL | NULL | 68 |

+------+------+------+

7 rows in set (0.00 sec)