是否有一种方法可以提高查询的性能，该查询在SELECT子句中使用多个子Query

Question

我有一个查询，该查询从两个表_1和table_2中选择数据。两个表与ID连接。表_1中的每个行都可以在表_2上完全具有4行。客户需要一个视图，该视图从表_1返回所有数据和表_2的所有数据，但必须像同一行中的列一样显示来自表_2的数据。例如：

这是我使用过的查询：

Select ID, NAME, LASTNAME,
(SELECT HomeType from #TABLE_2 where id = t1.ID order by ID OFFSET 0 ROW FETCH NEXT 1 ROW ONLY) as Row1Column1,
(SELECT HomeCost from #TABLE_2 where id = t1.ID order by ID OFFSET 0 ROW FETCH NEXT 1 ROW ONLY) as Row1Column2,
(SELECT HomeType from #TABLE_2 where id = t1.ID order by ID OFFSET 1 ROW FETCH NEXT 1 ROW ONLY) as Row2Column1,
(SELECT HomeCost from #TABLE_2 where id = t1.ID order by ID OFFSET 1 ROW FETCH NEXT 1 ROW ONLY) as Row2Column2,
(SELECT HomeType from #TABLE_2 where id = t1.ID order by ID OFFSET 2 ROW FETCH NEXT 1 ROW ONLY) as Row2Column1,
(SELECT HomeCost from #TABLE_2 where id = t1.ID order by ID OFFSET 2 ROW FETCH NEXT 1 ROW ONLY) as Row2Column2,
(SELECT HomeType from #TABLE_2 where id = t1.ID order by ID OFFSET 3 ROW FETCH NEXT 1 ROW ONLY) as Row2Column1,
(SELECT HomeCost from #TABLE_2 where id = t1.ID order by ID OFFSET 3 ROW FETCH NEXT 1 ROW ONLY) as Row2Column2 from #TABLE_1 as t1 

我的数据的一个示例是

CREATE TABLE #TABLE_1(ID int, NAME varchar(100), LASTNAME varchar(100))
CREATE TABLE #TABLE_2(ID int,HomeType varchar(100), HomeCost varchar(100) )

Insert into #TABLE_1 (ID, NAME, LASTNAME) VALUES (1, 'JOHN', 'SNOW')
Insert into #TABLE_2 (ID, HomeType, HomeCost) VALUES (1, 'Type1', 'Cost1')
 Insert into #TABLE_2 (ID, HomeType, HomeCost) VALUES (1, 'Type2', 'Cost2')
 Insert into #TABLE_2 (ID, HomeType, HomeCost) VALUES (1, 'Type3', 'Cost3')
 Insert into #TABLE_2 (ID, HomeType, HomeCost) VALUES (1, 'Type4', 'Cost4')

我想要这样的结果，但是对于50000行，它是慢速

ID| NAME| ROW1COLUMN1 | ROW1COLUMN2 | ROW2COLUMN1 | ROW2COLUMN2 | ROW3COLUMN1 | ROW3COLUMN2 | ROW4COLUMN1 | ROW4COLUMN2
-------------------------
 1|JOHN | Type1       | Cost1       | Type2       | Cost2       | Type3     | Cost3         | Type4       | Cost4

Answer 1

我没有测试此查询，但应该起作用。不要忘记为ID的祖先字段做索引。如果字段" hometype"是动态的，则需要添加其他字段'pos'。" pos"字段对于具有相同ID的表_2中的行应该唯一。

SELECT t.ID, t.NAME, t.LASTNAME, t1.HomeType AS Row1Column1, t1.HomeCost AS Row1Column2, t2.HomeType AS Row2Column1, t2.HomeCost AS Row2Column2, t3.HomeType AS Row3Column1, t3.HomeCost AS Row3Column2, t4.HomeType AS Row4Column1, t4.HomeCost AS Row4Column2
FROM TABLE_1 t
JOIN TABLE_2 t1 ON t.ID = t1.ID AND t1.HomeType = 'Type1'
JOIN TABLE_2 t2 ON t.ID = t2.ID AND t2.HomeType = 'Type2'
JOIN TABLE_2 t3 ON t.ID = t3.ID AND t3.HomeType = 'Type3'
JOIN TABLE_2 t4 ON t.ID = t4.ID AND t4.HomeType = 'Type4'

Answer 2

如果您的四种类型相同，则可以使用条件聚合：

select t1.id, t1.name, t1.lastname,
       max(case when t2.type = 'type1' then hometype end) as hometype_1,
       max(case when t2.type = 'type1' then homecost end) as homecost_1,
       max(case when t2.type = 'type2' then hometype end) as hometype_2,
       max(case when t2.type = 'type2' then homecost end) as homecost_2,
       max(case when t2.type = 'type3' then hometype end) as hometype_3,
       max(case when t2.type = 'type3' then homecost end) as homecost_3,
       max(case when t2.type = 'type4' then hometype end) as hometype_4,
       max(case when t2.type = 'type4' then homecost end) as homecost_4
from table1 t1 left join
     t2
     on t1.id = t2.id
group by t1.id, t1.name, t1.lastname;

Answer 3

您可以使用Pivot

WITH T2 AS (
    SELECT 
        ID, 
        HomeTypeRN = 'A' + CAST( ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HomeType) AS VARCHAR),
        HomeType, 
        HomeCostRN = 'B' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY HomeType) AS VARCHAR), 
        HomeCost
    FROM #TABLE_2
)
SELECT 
    T1.ID, T1.Name, T1.LastName, [A1], [B1], [A2], [B2], [A3], [B3], [A4], [B4]
FROM #TABLE_1 T1
LEFT JOIN
(
    SELECT ID, 
    MAX(A1) AS A1, MAX(B1) AS B1, 
    MAX(A2) AS A2, MAX(B2) AS B2, 
    MAX(A3) AS A3, MAX(B3) AS B3, 
    MAX(A4) AS A4, MAX(B4) AS B4 
    FROM T2 
    PIVOT(
        MAX(HomeType) FOR HomeTypeRN IN ([A1],[A2],[A3],[A4])
    ) P1
    PIVOT(
        MAX(HomeCost) FOR HomeCostRN IN ([B1],[B2],[B3],[B4])
    ) P2
    GROUP BY ID
) P  ON P.ID = T1.ID