sql - 如果 B 列为 Null，如何从 A 列插入数据；如果 A 列为 Null，如何从 B 列插入数据

Question

我想我接近了但不确定:

我有一个包含电子邮件地址、个人和工作的示例表。我正在尝试创建一个包含单个地址的新表。

然后插入一个电子邮件地址，例如:如果用户有工作电子邮件但没有个人电子邮件，则插入工作电子邮件，如果没有工作电子邮件，则插入个人电子邮件。

下面的代码不起作用，但它只是为了展示我使用过的许多变体中的一部分:

使用 EXAMPLE_DB

开始

CREATE TABLE USER_EMAIL(
 USER_NBR CHAR(1) NOT NULL
,EMAIL VARCHAR(30)NULL
)

GO

INSERT INTO USER_EMAIL(
 USER_NBR
,EMAIL
)

SELECT USER_NBR
,CASE WHEN EMAIL_ADDR IS NULL AND EMAIL_TYPE = 'Personal' THEN EMAIL_TYPE = 'Work' END EMAIL
,CASE WHEN EMAIL_ADDR IS NULL AND EMAIL_TYPE = 'Work'     THEN EMAIL_TYPE =  'Personal' END EMAIL
FROM EMAIL_DATA

这是 EMAIL_DATA 的表格:

USER_NBR   EMAIL_TYPE       EMAIL_ADDR
1          Personal 
2          Personal     user2personal@demo.com
3          Personal         user3personal@demo.com
4          Personal 
5          Personal         user5personal@demo.com
1          Work         user1work@demo.com
2          Work         user2work@demo.com
3          Work 
4          Work         user4work@demo.com
5          Work 

谢谢大家!

Answer 1

这是一个使用 row_number 的例子:

insert  user_email
        (user_nbr, email)
select  user_nbr
,       mail.email_addr
from    (
        select  user_nbr
        ,       email_addr
        ,       row_number() over (partition by user_nbr
                  order by
                  case 
                  when email_type = 'personal' then 1
                  when email_type = 'work' then 2
                  end) as rn
        from    email_data
        ) usr
where   usr.rn = 1

由于只有两种电子邮件类型，您还可以使用 left join 两次:

insert  user_email
        (user_nbr, email)
select  user_nbr
,       coalesce(pers.email_addr, work.email_addr)
from    (
        select  distinct user_nbr
        from    email_data
        ) usr
left join 
        email_data as pers 
on      pers.user_nbr = usr.user_nbr
        and work.email_type = 'personal'
left join 
        email_data as work
on      work.user_nbr = usr.user_nbr
        and work.email_type = 'work'

另一种使用交叉应用查找首选电子邮件地址的方法:

insert  user_email
        (user_nbr, email)
select  user_nbr
,       mail.email_addr
from    (
        select  distinct user_nbr
        from    email_data
        ) usr
cross apply
        (
        select  top 1 *
        from    email_data mail
        where   mail.user_nbr = usr.user_nbr
                and mail.email_addr is not null
        order by
                case 
                when email_type = 'personal' then 1
                when email_type = 'work' then 2
                end
        ) mail