T-sql select语句-基于另一个表数据的具有Case语句yes或no的多列

Question

我有一个包含多列的T-SQL select语句，其中Case语句基于另一个表数据为"是"或"否">

我有这两张桌子：

表1

Id  | Location
----+------------
101 | Warsaw
102 | Belfast
104 | Singapore
106 | Texas
108 | Mumbai
109 | Virgina

表2：

Id  | AttrName   | AttrId
----+------------+---------
101 | Datacenter | 99
101 | Cold       | 88
102 | Datacenter | 99
102 | Cold       | 88
106 | Datacenter | 99
108 | Cold       | 88

查询输出应为：

Id  | Location  | Datacenter | Cold 
----+-----------+------------+------
101 | Warsaw    | YES        | YES 
102 | Belfast   | YES        | YES 
104 | Singapore | NO         | NO  
106 | Texas     | YES        | NO  
108 | Mumbai    | NO         | YES 
109 | Virgina   | NO         | NO  

请让我知道我们如何才能做到这一点。

Answer 1

我会使用PIVOT。它可以表现得更好，但我会检查每一个的执行计划以确定。在比较计划之前，请确保将我的查询重定向到真实的表(而不是用于测试的这些表变量(。祝你好运！：(

DECLARE @Table1 TABLE (
    Id BIGINT NOT NULL PRIMARY KEY,
    Location VARCHAR(100) NOT NULL,
    UNIQUE CLUSTERED (Id)
);
INSERT INTO @Table1
(Id, Location)
VALUES
(101,'Warsaw'),
(102,'Belfast'),
(104,'Singapore'),
(106,'Texas'),
(108,'Mumbai'),
(109,'Virgina');
DECLARE @Table2 TABLE (
    Id BIGINT NOT NULL,
    AttrName VARCHAR(100) NOT NULL,
    AttrId BIGINT NOT NULL,
    UNIQUE CLUSTERED (Id,AttrId)
);
INSERT INTO @Table2
(Id, AttrName, AttrId)
VALUES
(101,'Datacenter',99),
(101,'Cold',88),
(102,'Datacenter',99),
(102,'Cold',88),
(106,'Datacenter',99),
(108,'Cold',88);
WITH CTE_Table2 AS (
    SELECT
        Id,
        Datacenter,
        Cold
    FROM (
        SELECT
            t.Id,
            t.AttrName,
            'YES' AS Bool
        FROM @Table2 AS t
    ) AS b
    PIVOT (
        MAX(Bool)
        FOR AttrName IN ([Datacenter],[Cold])
    ) AS p
)
SELECT
    t1.Id,
    t1.Location,
    ISNULL(t2.Datacenter,'NO') AS Datacenter,
    ISNULL(t2.Cold,'NO') AS Cold
FROM @Table1 AS t1
LEFT JOIN CTE_Table2 AS t2
    ON t1.Id = t2.Id;

Answer 2

下面是其他要解决的选项，它使用EXISTS、

-- delcare table variables
declare @t1 table (Id int, Location nvarchar(20))
declare @t2 table (Id int, AttrName nvarchar(20), AttrId int)
-- insert test data
insert into @t1
values (101, 'Warsaw'),
    (102, 'Beflast'),
    (104, 'Singapore'),
    (106, 'Texas'),
    (108, 'Mumbai'),
    (109, 'Virginia')
insert into @t2
values (101, 'Datacentre', 99),
    (101, 'Cold', 88),
    (102, 'Datacentre', 99),
    (102, 'Cold', 88),
    (106, 'Datacentre', 99),
    (108, 'Cold', 88)
-- query
select 
    t1.Id,
    Location, 
    case when exists (select 1 from @t2 t2 where t2.AttrId = 99 and t1.id = t2.id) then 'yes' else 'no' end as Datacentre,
    case when exists (select 1 from @t2 t2 where t2.AttrId = 88 and t1.id = t2.id) then 'yes' else 'no' end as Cold
from @t1 t1

结果

Id  Location    Datacentre  Cold
101 Warsaw          yes     yes
102 Beflast         yes     yes
104 Singapore       no      no
106 Texas           yes     no
108 Mumbai          no      yes
109 Virginia        no      no

Answer 3

1. 简单但未优化的方法：您需要将OUTER JOIN table2到table1的数量是您希望在最终结果中放入不同列的AttrName的数量的几倍。每个联接都应该为表2使用不同的别名，并对特定的AttrName进行筛选：

FROM table1 as base
LEFT OUTER JOIN table2 as Datacenter on base.Id = Datacenter.Id and Datacenter.AttrName = 'Datacenter'
LEFT OUTER JOIN table2 as Cold on base.Id = Cold.Id and Cold.AttrName = 'Cold'

在SELECT中，您需要检查是否存在与表2的特定别名匹配的查找，以定义从AttrName派生的列：

CASE WHEN Datacenter.Id IS NOT NULL THEN 'YES' ELSE 'NO' END as Datacenter

2. 高级且可能更优化的方法：
   使用Utrolig的答案：PIVOT应该因为查找较少而表现出色(但在优化代码时始终查看查询计划！(