关于 sql：有没有办法提高在 select 子句中使用多个子查询的查询的性能

Is there a way to increase performance of a query which uses multiple subquerys in select clause

我有一个查询，它从两个表 TABLE_1 和 TABLE_2 中选择数据。这两个表是用id连接的。 TABLE_1 中的每一行在 TABLE_2 上可以正好有 4 行。客户需要一个视图，该视图返回 TABLE_1 中的所有数据和 TABLE_2 中的所有数据，但 TABLE_2 中的数据必须显示为与同一行中的列一样。例如：

这是我用过的一个查询：

1
2
3
4
5
6
7
8
9

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

我的数据的一个例子是

1
2
3
4
5
6
7
8
9
10

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 行它会变慢

1
2
3

相关讨论

你应该研究 mysql 条件聚合。
您应该添加您尝试过的查询和您在表上拥有的索引
这里的大多数人都希望将样本数据和预期结果作为格式化文本，而不是图像(或图像链接)。
编辑您的问题并将您的查询作为文本放入问题中。
欢迎来到 StackOverflow！如果您提供 EXPLAIN …your query… 的输出，它也可能会有所帮助。
它实际上是哪个引擎？ MySQL 还是 SQLServer？您帖子上的标签都提到了两者。

我没有测试这个查询，但它应该可以工作。不要忘记为 ID、HomeType 字段做索引。
如果字段”HomeType”是动态的，您需要添加额外的字段”pos”。对于 TABLE_2 中具有相同 ID 的行，”pos”字段应该是唯一的。

1
2
3
4
5
6

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’

相关讨论

另一种(更复杂的)替代方法是在第二张表上进行旋转然后加入，但这种解决方案对于 OP 应该没问题。我建议更改 left 的内部联接，以防表 1 上的每条记录在表 2 上都有 4 个匹配行。
我不知道 TABLE_2 上 HomeType 列的正确值。在没有这种条件的情况下如何执行联接？
您能否在 TABLE_2 中添加其他字段？

您可以使用 PIVOT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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

如果你有相同的四种类型，那么你可以像这样使用条件聚合：

1
2
3
4
5
6
7
8
9
10
11
12
13

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;

来源：https://www.codenong.com/56041408/

Is there a way to increase performance of a query which uses multiple subquerys in select clause

猜你喜欢