时间戳

首页 > 解决方案 > sql group by 没有 sum 函数

sql - sql group by 没有 sum 函数

问题描述

我有这样的表Products：

    date       | product | amount | gross
1-) 10/03/2020 | TV      | 300    | 500  
2-) 10/03/2020 | Table   | 200    | 400
3-) 10/03/2020 | Phone   | 100    | 200
4-) 10/02/2020 | Table   | 40     | 215
5-) 10/02/2020 | Phone   | 35     | 100
6-) 10/01/2020 | Tv      | 145    | 420
7-) 10/12/2019 | Table   | 400    | 800
8-) 10/12/2019 | Tv      | 200    | 450

我想要做的是根据日期对表格进行分组，并在另一列中写入产品和金额

结果表应该是这样的：

        date   |product1|amount1|gross1|product2|amount2| gross2|product3|amount3 |gross3

1-) 10/03/2020 | TV     | 300   | 500  |Table   |200    |400    |Phone   |100     |200 
2-) 10/02/2020 | Table  |  40   | 215  |Phone   |35     |100    |null    |null    |null
3-) 10/01/2020 | Tv     | 145   | 420  |null    |null   |null   |null    |null    |null
4-) 10/12/2019 | Table  | 400   | 800  |Tv      |200    |450    |null    |null    |null

我尝试使用 group by 但它不起作用，因为我不需要 sum 函数来计算金额。你知道我能做什么吗？

select 
    date, product1, amount1, gross1,
    product2, amount2, gross2,
    product3, amount3, gross3 
from
    Products 
group by 
    date

标签： sqloracle

解决方案

For a fixed count of products per day, you can use conditional aggregation - but you need a column that defines the ordering of rows having the same date, I assumed id:

select date,
    max(case when rn = 1 then product end) as product1,
    max(case when rn = 1 then amount  end) as amount1,
    max(case when rn = 1 then gross   end) as gross1,
    max(case when rn = 2 then product end) as product2,
    max(case when rn = 2 then amount  end) as amount2,
    max(case when rn = 2 then gross   end) as gross2,
    max(case when rn = 3 then product end) as product3,
    max(case when rn = 3 then amount  end) as amount3,
    max(case when rn = 3 then gross   end) as gross3
from (
    select t.*,
        row_number() over(partition by date order by id) as rn
    from mytable t
) t
group by date
order by date desc

推荐阅读