SQL Server在联接两个表时永不终止

Question

我在DB中有一个源表。我需要进行分组和求和以获得一个桥接表，提取另一个桥接表格上的供应商信息，然后使用part_number将两者连接起来。

如果我单独运行子查询，T1会给我54699条记录，T2会给我大约10倍的T1行。

接下来，我确实离开了join，我预计它应该返回54699条记录，但服务器引擎从未停止，在我向下滚动到最后时，它返回了5000万条记录。我必须手动停止查询。我意识到我的查询一定有问题，但我想不通。如果你有任何想法，我将不胜感激。非常感谢。

SELECT 
T1.*, T2.SUPPLIER 
FROM 
(SELECT 
T.PART_NUMBER,T.YEAR, T.WEEK, 
SUM(T.QTY_FILLED) TOTAL_FILLED,
SUM(T.QTY_ORDERED) TOTAL_ORDERED, 
COUNT(T.LINE_NUMBER) ORDER_TIMES
FROM 
DBO.TABLE1 T
WHERE 
T.YEAR IS NOT NULL 
GROUP BY 
PART_NUMBER, T.YEAR, T.WEEK) T1
LEFT JOIN 
(SELECT 
T.PART_NUMBER, T.SUPPLIER
FROM 
DBO.TABLE1 T) T2 ON T1.PART_NUMBER = T2.PART_NUMBER 
ORDER BY 
T1.PART_NUMBER, T1.YEAR, T1.WEEK

我也试过窗口功能，但仍然没有成功。

WITH T1 AS 
(
SELECT 
T.PART_NUMBER,T.YEAR, T.WEEK, 
SUM(T.QTY_FILLED) TOTAL_FILLED,
SUM(T.QTY_ORDERED) TOTAL_ORDERED, 
COUNT(T.LINE_NUMBER) ORDER_TIMES
FROM 
DBO.TABLE1 T
WHERE 
T.YEAR IS NOT NULL 
GROUP BY 
PART_NUMBER, T.YEAR, T.WEEK
), T2 AS
(
SELECT T.PART_NUMBER, T.SUPPLIER
FROM DBO.TABLE1 T
)
SELECT 
T1.*, T2.SUPPLIER 
FROM 
T1 
LEFT JOIN 
T2 ON T1.PART_NUMBER = T2.PART_NUMBER 
ORDER BY 
T1.PART_NUMBER, T1.YEAR, T1.WEEK

Answer 1

首先，它不仅返回54699行。您执行的联接没有distinct，因此结果可能是50.000 x 5.000.000行的联接，这取决于表的值。如果您使用SQL 2017或更新版本，请尝试以下操作：

SELECT 
T.PART_NUMBER,T.YEAR, T.WEEK, 
SUM(T.QTY_FILLED) TOTAL_FILLED,
SUM(T.QTY_ORDERED) TOTAL_ORDERED, 
COUNT(T.LINE_NUMBER) ORDER_TIMES,
STRING_AGG (SUPPLIER, ', ') AS SUPPLIER

FROM 
DBO.TABLE1 T
WHERE 
T.YEAR IS NOT NULL 
GROUP BY 
PART_NUMBER, T.YEAR, T.WEEK