SQL如何根据“较早日期”条件选择多列

Question

我正在尝试使用SQL转换下表：

person_id event1 date1        event2   date2
1         yes    2019-01-01   NULL     NULL
2         NULL   NULL         yes      2019-02-02
3         NULL   NULL         NULL     NULL
4         yes    2019-03-01   yes      2019-03-04

变成类似这样的东西：

person_id  event_overall  date_overall
1          event1         2019-01-01
2          event2         2019-02-02
3          no_event       NULL
4          event1         2019-03-01

基本上，如果event1为yes，event2为NULL，则显示event1，反之亦然。如果两者均为NULL，则显示no_event。如果两者均为yes，则显示较早的日期(示例中2019-03-01在2019-03-04之前，因此选择event1 )。

Answer 1

请尝试：

select 
    person_id, 
    case when event1 is null and event2 is null then 'no event' 
    when (event1 is not null and event2 is null) OR date1<date2 then 'event1'
    else 'event2'end event_overall, 
    case when event1 is null and event2 is null then null
    when (event1 is not null and event2 is null) OR date1<date2 then date1
    else date2 end date_overall
 from tbl

Answer 2

你可以这样试一下

SELECT 
CASE WHEN event1 = 'yes' THEN date1
     WHEN event2 = 'yes' THEN date2
     WHEN (event1 = 'yes' AND event2 = 'yes') THEN IF(date1 < date2, date1, date2)
     WHEN (event1 IS NULL AND event2 IS NULL) THEN 'no_event'
END AS DATE
FROM tb_event_test