类似Oracle中的rollup,不同的是不能排序,select后面查询的字段可以不是group by的字段。
SELECT a.actor_id,COUNT(1) cc FROM film_actor a,actor b
WHERE a.actor_id=b.actor_idAND a.actor_id<10
GROUP BY actor_id WITH ROLLUP;
+----------+-----+
| actor_id | cc |
+----------+-----+
| 1 | 19 |
| 2 | 25 |
| 3 | 22 |
| 4 | 22 |
| 5 | 29 |
| 6 | 20 |
| 7 | 30 |
| 8 | 20 |
| 9 | 25 |
| NULL | 212 |
+----------+-----+
SELECT a.actor_id,first_name,COUNT(1) cc FROM film_actor a,actor b
WHERE a.actor_id=b.actor_id
AND a.actor_id<10
GROUP BY actor_id WITH ROLLUP;
+----------+------------+-----+
| actor_id | first_name | cc |
+----------+------------+-----+
| 1 | PENELOPE | 19 |
| 2 | NICK | 25 |
| 3 | ED | 22 |
| 4 | JENNIFER | 22 |
| 5 | JOHNNY | 29 |
| 6 | BETTE | 20 |
| 7 | GRACE | 30 |
| 8 | MATTHEW | 20 |
| 9 | JOE | 25 |
| NULL | JOE | 212 |
+----------+------------+-----+
SELECT * FROM (
SELECT a.actor_id,COUNT(1) cc FROM film_actor a,actor b
WHERE a.actor_id=b.actor_id
AND a.actor_id<10
GROUP BY actor_id WITH ROLLUP) bb
ORDER BY cc;
+----------+-----+
| actor_id | cc |
+----------+-----+
| 1 | 19 |
| 6 | 20 |
| 8 | 20 |
| 3 | 22 |
| 4 | 22 |
| 2 | 25 |
| 9 | 25 |
| 5 | 29 |
| 7 | 30 |
| NULL | 212 |
+----------+-----+