The ROLLUP Operator | ROLLUP运算符 | MySQL

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