sql行转列实例
2024-09-18 20:21:17
select gh ,xm ,
max(A.bz) as bz ,
max(A.jcz) as jcz ,
max(A.dl) as dl ,
max(A.czzx) as czzx ,
max(A.jt) as jt ,
max(A.dt) as dt ,
max(A.jl) as jl ,
max(A.zycqbw) as zycqbw ,
max(A.zc) as zc ,
max(A.zxqbw) as zxqbw ,
max(A.lczx) as lczx
from(
select gh ,xm ,
CASE bgzlmc when '编组'then sum(cworkcount) else 0 end as bz ,
CASE bgzlmc when '解车组'then sum(cworkcount) else 0 end as jcz ,
CASE bgzlmc when '代量'then sum(cworkcount) else 0 end as dl ,
CASE bgzlmc when '车组转线'then sum(cworkcount) else 0 end as czzx ,
CASE bgzlmc when '解体'then sum(cworkcount) else 0 end as jt ,
CASE bgzlmc when '倒途'then sum(cworkcount) else 0 end as dt ,
CASE bgzlmc when '禁溜'then sum(cworkcount) else 0 end as jl ,
CASE bgzlmc when '作业车取摆位'then sum(cworkcount) else 0 end as zycqbw ,
CASE bgzlmc when '转场'then sum(cworkcount) else 0 end as zc ,
CASE bgzlmc when '站修取摆位'then sum(cworkcount) else 0 end as zxqbw ,
CASE bgzlmc when '列车转线'then sum(cworkcount) else 0 end as lczx
from t_dcd_confirmworkdetail left join t_dcd_personworkdetails
on t_dcd_confirmworkdetail.planid = t_dcd_personworkdetails.planid
left join t_dcd_bgzl on t_dcd_confirmworkdetail.bgzlid =t_dcd_bgzl.id
where 1=1 and ctime between '2018-09-01' and '2018-09-19' group by gh, xm,bgzlmc )A group by gh,xm
转换前数据:
转换后数据:
最新文章
- LeetCode刷刷记录
- 《Qt Quick 4小时入门》学习笔记2
- C# js jquery复制textbox内容总结
- Odoo attrs X2many 类型的过滤
- AVPlayer的使用本地视频
- yii2 debug工具条不出现
- Camel、Pastal、匈牙利标记法
- 一个Web页面的生命周期 ,面试常常被问到
- 拍照、本地图片工具类(兼容至Android7.0)
- java架构师负载均衡、高并发、nginx优化、tomcat集群、异步性能优化、Dubbo分布式、Redis持久化、ActiveMQ中间件、Netty互联网、spring大型分布式项目实战视频教程百度网盘
- 前端系列——jquery前端国际化解决方案“填坑日记”
- 熵(Entropy),交叉熵(Cross-Entropy),KL-松散度(KL Divergence)
- 如何搞定SVN目录的cleanup问题和lock问题
- BZOj 4540: [Hnoi2016]序列 [莫队 st表 预处理]
- 【LightOJ1336】Sigma Function(数论)
- myBatis源码学习之SqlSession
- Unity正交模式摄像机与屏幕适配的方法
- 解决VS Code使用code runner开发Python乱码问题
- Jvm 性能检测
- Power BI 与 Azure Analysis Services 的数据关联:4、Power BI 连接到Azure Analysis Services 并展示