复制 EXPLAIN
SELECT payment_date, SUM (amount)
FROM payment
GROUP BY payment_date;
+ -------------+-------------------------------+
|id | 1 |
+ -------------+-------------------------------+
|select_type | SIMPLE |
+ -------------+-------------------------------+
| table |payment |
+ -------------+-------------------------------+
|partitions | NULL |
+ -------------+-------------------------------+
| type |ALL |
+ -------------+-------------------------------+
|possible_keys| NULL |
+ -------------+-------------------------------+
| key | NULL |
+ -------------+-------------------------------+
|key_len | NULL |
+ -------------+-------------------------------+
|ref | NULL |
+ -------------+-------------------------------+
| rows | 16086 |
+ -------------+-------------------------------+
|filtered | 100 |
+ -------------+-------------------------------+
|Extra | Using temporary; Using filesort|
+ -------------+-------------------------------+
在 Extra 列中的 “Using filesort” 表明了上述 SQL 语句含有排序语义,若想要去除排序语义,在 SQL 语句后面添加一个 ORDER BY NULL 子句:
复制 EXPLAIN
SELECT payment_date, SUM (amount)
FROM payment
GROUP BY payment_date
ORDER BY NULL ;
+ -------------+---------------+
|id | 1 |
+ -------------+---------------+
|select_type | SIMPLE |
+ -------------+---------------+
| table |payment |
+ -------------+---------------+
|partitions | NULL |
+ -------------+---------------+
| type |ALL |
+ -------------+---------------+
|possible_keys| NULL |
+ -------------+---------------+
| key | NULL |
+ -------------+---------------+
|key_len | NULL |
+ -------------+---------------+
|ref | NULL |
+ -------------+---------------+
| rows | 16086 |
+ -------------+---------------+
|filtered | 100 |
+ -------------+---------------+
|Extra | Using temporary|
+ -------------+---------------+
复制 SELECT
YEAR (rental_date) y,
MONTH (rental_date) m,
COUNT ( * ) num
FROM rental
GROUP BY y, m
WITH ROLLUP ;
复制 +----+----+-----+
|y |m |num |
+----+----+-----+
|2005|5 |1156 |
|2005|6 |2311 |
|2005|7 |6709 |
|2005|8 |5686 |
|2005|NULL|15862|
|2006|2 |182 |
|2006|NULL|182 |
|NULL|NULL|16044|
+----+----+-----+
注:根据上面的实例,可以推断出 GROUP BY y, m WITH ROLLUP ,相当于 GROUP BY y ,GROUP BY y, m 和 GROUP BY NULL(无 GROUP BY) 三者的 UNION(并集), 即等价于以下语句:
复制 (
SELECT YEAR (rental_date) y, MONTH (rental_date) m, COUNT ( * ) num
FROM rental
GROUP BY y, m
) UNION ALL (
SELECT YEAR (rental_date) y, NULL , COUNT ( * ) num
FROM rental
GROUP BY y
) UNION ALL (
SELECT NULL , NULL , COUNT ( * ) num
FROM rental
);