ROLLUP
案例一
同样的情景,分单组列时
现在来看一下加入WITH ROLLUP
会得到怎样的查询
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP
可以看到,这里给出了所有客户的总销售额
案例二
分多组列时
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY state,city WITH ROLLUP
这里的ROLLUP
可以理解为EACH ROLL
即会得到对每一个组及整个结果集的汇总值
来看一下没有ROLLUP
的返回值,理解一下
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY state,city -- WITH ROLLUP
注意: ROLLUP无法在SQL server和oracle里查询
练习
写一段查询,返回这个结果
涉及payment表格
我的答案
SELECT
name,
SUM(amount) AS total
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY name with rollup
修改注意:
- 列名需要给name加上AS payment_method
- 在使用ROLLUP运算符时,不可以在group by子句种使用列别名,因此注意代码最后一行依旧是GROUP BY name WITH ROLLUP