mysql case when 换行

MySql 码拜 6年前 (2016-02-04) 713次浏览
1.waybill_number和express_company_id构成唯一性(看成一单货物),每个货物对应到件时间和签收时间,本人想达到行转列的效果。但是到件时间和签收时间只会有一个有值,另外一个为零。主sql和查询结果如下图。

select t3.store_name 门店名称
      ,t3.full_name 货源品牌
      ,t3.waybill_number,
       (case  t3.scan_type when "p_scan_come" then t3.scan_time else 0 end) as 到件时间,
       (case  t3.scan_type when "p_scan_sign" then t3.scan_time else 0 end) as 签收时间       
  from       
       (select t1.store_name,t1.waybill_number,t1.express_company_id,t1.full_name,t1.scan_type,t1.scan_time
          from 
              (select a.store_name,c.waybill_number,c.express_company_id,d.full_name,c.scan_type,c.scan_time
                 from tb_store a 
           inner join tb_store_exp_axp_rel b 
                   on a.store_name = "华东理工大学奉贤校区店" 
                  and a.type=2 
                  and a.store_id=b.sto_axp_id
           inner join tb_scan c              
                   on  b.sto_exp_id=c.scan_side 
                  and c.scan_time between "2016-06-01 00:00:00" 
                                      and "2016-06-03 23:59:59"
           inner join tb_express_company d   
                   on  c.express_company_id=d.express_company_id) t1
   inner join 
             (select waybill_number,express_company_id,scan_type,max(scan_time) scan_time 
                from tb_scan 
            group by waybill_number,express_company_id,scan_type) t2
          on  t1.waybill_number=t2.waybill_number
          and t2.express_company_id=t2.express_company_id
          and t1.scan_time=t2.scan_time
          and t1.scan_type=t2.scan_type
   group by t1.store_name,t1.full_name,t1.scan_time) t3
where t3.waybill_number=390107955209
group by t3.store_name,t3.full_name,t3.waybill_number

mysql case when 换行
t3查询结果如下图

select t1.store_name,t1.waybill_number,t1.express_company_id,t1.full_name,t1.scan_type,t1.scan_time
          from 
              (select a.store_name,c.waybill_number,c.express_company_id,d.full_name,c.scan_type,c.scan_time
                 from tb_store a 
           inner join tb_store_exp_axp_rel b 
                   on a.store_name = "华东理工大学奉贤校区店" 
                  and a.type=2 
                  and a.store_id=b.sto_axp_id
           inner join tb_scan c              
                   on  b.sto_exp_id=c.scan_side 
                  and c.scan_time between "2016-06-01 00:00:00" 
                                      and "2016-06-03 23:59:59"
           inner join tb_express_company d   
                   on  c.express_company_id=d.express_company_id) t1
   inner join 
             (select waybill_number,express_company_id,scan_type,max(scan_time) scan_time 
                from tb_scan 
            group by waybill_number,express_company_id,scan_type) t2
          on  t1.waybill_number=390107955209
          and t1.waybill_number=t2.waybill_number
          and t2.express_company_id=t2.express_company_id
          and t1.scan_time=t2.scan_time
          and t1.scan_type=t2.scan_type
   group by t1.store_name,t1.full_name,t1.scan_time

mysql case when 换行

解决方案

40

表达式列,取最大值,才会合并相同的group by 列
看联机group by 用法

CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明mysql case when 换行
喜欢 (0)
[1034331897@qq.com]
分享 (0)