MySQL GROUP BY WITH ROLLUP

￡神魔★判官ぃ 2022-05-31 01:08 201阅读 0赞

类似Oracle中的rollup，不同的是不能排序，select后面查询的字段可以不是group by的字段。

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;  
\+----------+-----+  
| 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 |  
\+----------+-----+