sql - FROM 子句中的 CASE 语句

Question

请参阅下面的 DDL:

CREATE TABLE dbaddress
(aid integer identity not null, link_id int, link_type char, primary key (aid))
CREATE TABLE dbDoorSupervisor
(did integer identity not null, name varchar(30), primary key (did))
CREATE TABLE dbLicensee
(lid integer identity not null, name varchar(30), primary key (lid))

INSERT INTO dbDoorSupervisor (name) values ('Ian')
INSERT INTO dbLicensee (name) values ('Maria')
INSERT INTO dbaddress (link_id, link_type) values (1,'D')
INSERT INTO dbaddress (link_id, link_type) values (1,'L')

我正在尝试根据提供的 Address.AID 获取门禁主管或被许可人的姓名。例如，如果在 WHERE 子句中提供援助 1，则从门监督表返回 Ian，但如果在 WHERE 子句中提供援助 2，则从被许可方表返回 Maria。

我知道您可以在 SELECT 子句中使用 CASE 语句，但是您可以在 FROM 子句中使用它们吗，即根据提供的 AID 从地址连接到被许可人或从地址连接到门口管理员？

Answer 1

您可以在左外连接部分中进行切换，如下所示:

select
    isnull(d.name, l.name) as name
from dbaddress as a
    left outer join dbDoorSupervisor as d on d.did = a.link_id and a.link_type = 'D'
    left outer join dbLicensee as l on l.lid = a.link_id and a.link_type = 'L'

或者无论如何加入并在 case 语句中切换

select
    case a.link_type
        when 'D' then d.name
        when 'L' then l.name
    end as name
from dbaddress as a
    left outer join dbDoorSupervisor as d on d.did = a.link_id
    left outer join dbLicensee as l on l.lid = a.link_id

如果要显示多于一列，则可以使用外部应用，这样就不必重复大小写:

select
    c.name, c.address, c.second_name
from dbaddress as a
    left outer join dbDoorSupervisor as d on d.did = a.link_id
    left outer join dbLicensee as l on l.lid = a.link_id
    outer apply (
        select d.name, d.second_name, d.address where a.link_type = 'D' union all
        select l.name, l.second_name, l.address where a.link_type = 'L'
    ) as c