我想替換以下查詢，因為我有性能問題。由于表池操作者（Lazy Spool）的存在，執行計劃給我帶來了大量的重視
。SELECT DISTINCT LOT_0,
VarCode = STUFF(
(
SELECT DISTINCT ', '/span> IT6.ID_0
FROM TOLOT S
INNER JOIN ETMM I ON I.ITMREF_0= S.ITMREF_0
INNER JOIN SICOD6 IT6 ON IT6.ID_0=I.TSICOD_6
WHERE IT6. LNGDES_0 <>'' AND LOT_0 < > '' AND TSICOD_0 = 'OP'
AND S.LOT_0=S2.LOT_0
FOR XML PATH (''), TYPE).value (' 。 [1]', 'nvarchar(max)'), 1, 2, '')
),
VarShort = STUFF(
(
SELECT DISTINCT ', ' IT6.SHODES_0
FROM TOLOT S
INNER JOIN ETMM I ON I.ITMREF_0= S.ITMREF_0
INNER JOIN SICOD6 IT6 ON IT6.ID_0=I.TSICOD_6
WHERE IT6. LNGDES_0 <>'' AND LOT_0 < > '' AND TSICOD_0 = 'OP'
AND S.LOT_0=S2.LOT_0
FOR XML PATH (''), TYPE).value (' 。 [1]', 'nvarchar(max)'), 1, 2, '')
)
,VarLong = STUFF(
(
SELECT DISTINCT ', ' IT6.LNGDES_0
FROM TOLOT S
INNER JOIN ETMM I ON I.ITMREF_0= S.ITMREF_0
INNER JOIN SICOD6 IT6 ON IT6.ID_0=I.TSICOD_6
WHERE IT6. LNGDES_0 <>'' AND LOT_0 < > '' AND TSICOD_0 = 'OP'
AND S.LOT_0=S2.LOT_0
FOR XML PATH (''), TYPE).value (' 。 [1]', 'nvarchar(max)'), 1, 2, '')
)
FROM TOLOT AS S2
GROUP BY LOT_0
我將感謝您的想法
uj5u.com熱心網友回復：
因為你是在SQL Server 2017上，你可以簡單地使用STRING_AGG
SELECT
S.LOT_0,
VarCode = STRING_AGG(IT6.ID_0, ', ')。
VarShort = STRING_AGG(IT6.SHODES_0, ', ')。
VarLong = STRING_AGG(IT6.LNGDES_0, ' , ')
FROM TOLOT AS S
LEFT JOIN ETMM I
(INNER JOIN SICOD6 IT6 ON IT6.ID_0 = I.TSICOD_6)
ON I.ITMREF_0 = S.ITMREF_0
AND IT6. LNGDES_0 <> '' AND TSICOD_0 = 'OP'
WHERE S.LOT_0 <>/span> ''
GROUP BY
S.LOT_0。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/311098.html
標籤：
上一篇：編輯插入的表sql