查询累计销售额案例（级联查询）

最新推荐文章于 2023-01-20 19:21:04 发布

AngellYue

最新推荐文章于 2023-01-20 19:21:04 发布

阅读量656

点赞数

分类专栏： hive与SQL的暧昧文章标签：查询累计销售额案例级联查询

本文链接：https://blog.csdn.net/JAVAJJX06012002/article/details/79002249

版权

hive与SQL的暧昧专栏收录该内容

3 篇文章 0 订阅

订阅专栏

查询累计销售额案例（级联查询）

店铺月份总销售额累计到当月的总销售额
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 |
+——-+——–+——-+———+–+