Mysql 用于将具有多个状态更改日期列的单行转换为具有状态和时间戳的多行的SQL查询

发布于12月07日

我的输入数据如下:

story_id	sched_state	story_being_groomed_ts	story_def_ts	story_in_prgrs_ts	story_cmpl_ts
1	completed	2023-01-01	2023-01-13	2023-01-20	2023-01-23
2	inprogress	2023-01-01	2023-01-10	2023-01-30

我希望我的输出是这样的:

根据时间戳可用性，我们总共有4个阶段(已整理、已定义、正在进行和已完成)，我们需要在输出中更新这些阶段.

story_id	sched_state	last_up_ts
1	Groomed	2023-01-01
1	Defined	2023-01-13
1	inprogress	2023-01-20
1	Completed	2023-01-23
2	Groomed	2023-01-01
2	Defined	2023-01-10
2	inprogress	2023-01-30

你能帮我拿一下这个吗？

推荐答案

在MySQL中没有直接取消透视的方法.可以按如下方式完成:

select story_id, sched_state, last_up_ts
from (
    select t.story_id,
      c.sched_state,
      case c.sched_state
        when 'Groomed' then story_being_groomed_ts
        when 'defined' then story_def_ts
        when 'inprogress' then story_in_prgrs_ts
        when 'completed' then story_cmpl_ts
      end as last_up_ts
    from mytable t
    cross join
    (
      select 'Groomed' as sched_state
      union all select 'defined'
      union all select 'inprogress'
      union all select 'completed'
    ) c
) sub
where last_up_ts is not null 
ORDER BY story_id, sched_state, last_up_ts