postgresql 如何在SQL中将列分组并将列透视为值

wr98u20j 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(98)

我在PostgreSQL上有这样的查询结果：
| 日期|出发|蓝队|红队|绿色_团队|
| --|--|--|--|--|
| 2023-08-11 2023-08-11|南| 2158 | 2832 | 2481 |
| 2023-08-11 2023-08-11|东| 6641 | 6714 | 6581 |
| 2023-08-11 2023-08-11|北| 1159 | 1512 | 1485 |
| 2023-08-11 2023-08-11|西| 8274 | 7131 | 8068 |
我需要组团队的新列像team_type和总和值考虑它。
所以我的首选输出应该是这样的：x1c 0d1x
我的sql脚本：

with res as (
select
    date,
    depart,
    case
        when team_name = 'blue' then scores
    end blue_team,
    case
        when team_name = 'red' then scores
    end red_team,
    case
        when team_name = 'green' then scores
    end green_team
from results)
select
    date,
    depart,
    sum(blue_team) blue_team,
    sum(red_team) red_team,
    sum(green_team) green_team
from res
group by 1,2

字符串

postgresql

来源：https://stackoverflow.com/questions/77412420/how-to-group-and-pivot-columns-to-values-in-sql

1条答案

按热度按时间

qeeaahzv1#

那么简单的union all就可以回答你的问题：

select date, depart, 'blue_team' as teamtype, blue_team as value from game_results
union all 
select date, depart, 'red_team' as teamtype, red_team as value from game_results
union all
select date, depart, 'green_team' as teamtype, green_team as value from game_results

字符串
也可以使用unnest：

SELECT date, depart
, UNNEST(ARRAY['blue_team', 'red_team', 'green_team']) AS teamtype
, UNNEST(ARRAY[blue_team, red_team, green_team]) AS value
FROM game_results

型
| 日期|出发|团队型|值|
| --|--|--|--|
| 2023-08-11 2023-08-11|南|蓝队| 2158 |
| 2023-08-11 2023-08-11|东|蓝队| 6641 |
| 2023-08-11 2023-08-11|北|蓝队| 1159 |
| 2023-08-11 2023-08-11|西|蓝队| 8274 |
| 2023-08-11 2023-08-11|南|红队| 2832 |
| 2023-08-11 2023-08-11|东|红队| 6714 |
| 2023-08-11 2023-08-11|北|红队| 1512 |
| 2023-08-11 2023-08-11|西|红队| 7131 |
| 2023-08-11 2023-08-11|南|绿色_团队| 2481 |
| 2023-08-11 2023-08-11|东|绿色_团队| 6581 |
| 2023-08-11 2023-08-11|北|绿色_团队| 1485 |
| 2023-08-11 2023-08-11|西|绿色_团队| 8068 |
fiddle

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 如何在SQL中将列分组并将列透视为值

1条答案

相关问题

热门标签

最新问答