代码之家 › 专栏 › 技术社区 › Michi

如果表包含空值,则将列转换为查询中的行

sql mysql

Michi · 技术社区 · 5 年前

CREATE TABLE logistics (
    id int primary key,
    campaign VARCHAR(255),
    inbound_date VARCHAR(255),
    outbound_date VARCHAR(255)
);

INSERT INTO logistics
(id, campaign, inbound_date, outbound_date)
VALUES 
("1", "C001", "2019-01-01", "2019-02-08"),
("2", "C001", "2019-05-10", "2019-05-12"),
("3", "C001", "2019-06-12", NULL),
("4", "C001", NULL, "2019-06-15"),
("5", "C002", "2019-11-14", "2019-11-22"),
("6", "C002", NULL, "2019-12-13"),
("7", "C002", NULL, NULL);

inbound_date 和 outbound_date .
column 打电话 event_type .

预期结果

Campaign     event_type          event_date
C001         inbound_date        2019-01-01
C001         outbound_date       2019-02-08
C001         inbound_date        2019-05-10
C001         outbound_date       2019-05-12
C001         inbound_date        2019-06-12
C001         outbound_date       2019-06-15
C002         inbound_date        2019-11-14
C002         outbound_date       2019-11-22
C002         outbound_date       2019-12-13

首先,我试着使用解决方案表单 this question :

select campaign,
       (case when inbound_date is not null then 'inbound_date' 
             when outbound_date is not null then 'outbound_date'
        end) event_date,
       (case when inbound_date is not null then inbound_date
             when outbound_date is not null then outbound_date
        end) as event_date
from logistics;

第二,我试着用 here

SELECT campaign,
       coalesce(inbound_date, outbound_date) as event_date
FROM logistics;

对于这两个查询,我都没有得到任何结果。
我想问题是 NULL
我怎样才能得到预期的结果?

1 回复 | 直到 5 年前

Michi 5 年前

有一种方法:

select campaign, 'inbound_date' as event_type,  inbound_date as event_date
from logistics
where inbound_date is not null
union all
select campaign, 'outbound_date' as event_type,  outbound_date as event_date
from logistics
where outbound_date is not null;