sql - FROM 子句中的 CASE 语句

请参阅下面的 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 从地址连接到被许可人或从地址连接到门主管吗？

最佳答案

您可以像这样切换 left outer join 部分:

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

关于sql - FROM 子句中的 CASE 语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19326502/