时间戳

首页 > 解决方案 > mysql动态数据透视表组

mysql - mysql动态数据透视表组

问题描述

我正在从一些表中进行动态透视：

select 
spec.id, 
'Spec1', 
(if(DATE_FORMAT(schedule.data,'%Y-%m-%d') = '2019-11-12', DATE_FORMAT(data,'%H:%i'),0)) as "12-11-2019",
(if(DATE_FORMAT(schedule.data,'%Y-%m-%d') = '2019-11-14', DATE_FORMAT(data,'%H:%i'),0)) as "14-11-2019",
(if(DATE_FORMAT(schedule.data,'%Y-%m-%d') = '2019-11-19', DATE_FORMAT(data,'%H:%i'),0)) as "19-11-2019"
from service_spec inner join spec on spec.id = service_spec.spec_id
     left join schedule on service_spec.spec_id = schedule.spec_id 
     where spec.id = 506
     group by schedule.data
     order by spec.name

这是返回的查询

id  Spec1   12.11.2019  14.11.2019  19.11.2019
508 Spec1   10:00           0           0
508 Spec1   10:30           0           0
508 Spec1   11:00           0           0
508 Spec1   11:30           0           0
508 Spec1   12:00           0           0
508 Spec1   0               0           10:00
508 Spec1   0               0           10:30
508 Spec1   0               0           11:00
508 Spec1   0               0           11:30
508 Spec1   0               0           12:00

这是我的期望：

id  Spec1   12.11.2019  14.11.2019  19.11.2019
508 Spec1   10:00       0           10:00
508 Spec1   10:30       0           10:30
508 Spec1   11:00       0           11:00
508 Spec1   11:30       0           11:30
508 Spec1   12:00       0           12:00

我怎么能这样做？

标签： mysqlsqlpivot-table

解决方案

这有点棘手。您需要条件聚合，但您没有它的列。

您可以使用以下方法创建一个row_number()：

select sc_id, 'Spec1', 
       max(case when date(sc_data) = '2019-11-12'
                then date_format(sc_data, '%H:%i')
           end) as "12-11-2019",
       max(case when date(sc_data) = '2019-11-14'
                then date_format(sc_data, '%H:%i')
           end) as "14-11-2019",
       max(case when date(sc_data) = '2019-11-19'
                then date_format(sc_data, '%H:%i')
           end) as "19-11-2019"
from (select s.id, sc.data as sc_data,
             row_number() over (partition by date(sc.data) order by sc_data) as seqnum
      from service_spec ss inner join
           spec s
           on s.id = ss.spec_id left join
           schedule sc
           on ss.spec_id = sc.spec_id 
      where s.id = 506 and
            date(sc.data) in ('2019-11-12', '2019-11-14', '2019-11-19')
     ) s
group by seqnum
order by seqnum;

在 MySQL 8.0 之前的版本中，您可以对变量执行类似的操作。

推荐阅读