查询累计销售额案例(级联查询)
店铺 月份 总销售额 累计到当月的总销售额
a 01 350 350
a 02 5000 5350
a 03 600 5950
…….
数据样例:(店铺,月份,销售金额)
a,01,150
a,01,200
b,01,1000
b,01,800
c,01,250
c,01,220
b,01,6000
a,02,2000
a,02,3000
b,02,1000
b,02,1500
c,02,350
c,02,280
a,03,350
a,03,250
….
**代码**
SELECT tl.NAME
,tl.month
,max(tl.amount) AS month_amount
,sum(tr.amount) AS accumulate
FROM (
SELECT NAME
,month
,sum(sale) AS amount
FROM shop
GROUP BY NAME
,month
) tl
JOIN (
SELECT NAME
,month
,sum(sale) AS amount
FROM shop
GROUP BY NAME
,month
) tr ON tl.NAME = tr.NAME
WHERE tl.month >= tr.month
GROUP BY tl.NAME
,tl.month
ORDER BY tl.NAME
,tl.month;
+——-+——–+——-+———+–+
| shop | mouth | sale | amount |
+——-+——–+——-+———+–+
| a | 01 | 350 | 350 |
| a | 02 | 5000 | 5350 |
| a | 03 | 600 | 5950 |
| b | 01 | 7800 | 7800 |
| b | 02 | 2500 | 10300 |
| c | 01 | 470 | 470 |
| c | 02 | 630 | 1100 |
+——-+——–+——-+———+–+