数据库列转行显示。(oracle mysql)
表名 test
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
显示效果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
oracle
select year
,sum(decode(month,1,amount,0)) m1
,sum(decode(month,2,amount,0)) m2
,sum(decode(month,3,amount,0)) m3
,sum(decode(month,4,amount,0)) m4
from test
group by year;
mysql
select year ,
max(case when month='1' then amount else null end) m1,
max(case when month='2' then amount else null end) m2,
max(case when month='3' then amount else null end) m3,
max(case when month='4' then amount else null end) m4
from test group by year;
Old soldiers never die
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步