mysql中如何将结果行改为列

ghhaqwfi 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(319)

我有下面的结果集

Name  |   ID |  Total |  CityName
--------------------------------
A         1       2        ABC
--------------------------------
B         2       1        XYZ
--------------------------------
C         3       1        ABC
--------------------------------

如何显示以下结果

Name |  ID  |   ABC |  XYZ
---------------------------
A       1        2      0
---------------------------
B       2        0      1
---------------------------
C       3        1      0
---------------------------

mysql

来源：https://stackoverflow.com/questions/52773837/how-to-change-result-row-into-column-in-mysql

2条答案

按热度按时间

bpsygsoo1#

好吧，它看起来像是一个子查询，或过程将是一个好主意。比如：

select Name,ID,
(select Total from tets where CityName ='ABC' and Id = t.Id) as 'ABC',
(select Total from tets where CityName ='XYZ' and Id = t.Id) as 'XYZ'
from tets t

赞(0）回复(0）举报 2021-06-18

8ehkhllq2#

使用条件聚合

select name, id, max(case when CityName='ABC' then total else 0 end) as ABC
max(case when CityName='XYZ' then total else 0 end) as XYZ
from tablename
group by name,id

赞(0）回复(0）举报 2021-06-18

我来回答

mysql中如何将结果行改为列

2条答案

相关问题

热门标签

最新问答