從表中獲取逗號分隔的一組值，其中另一個表上的另一個參考值出現兩次（或更多）

假設 SQL Server 2014 中的資料庫設定如下：

DECLARE @MATERIAL TABLE (ID int, CODE varchar(30));

INSERT @MATERIAL (ID, CODE) VALUES
(1, 'D3033MBBY'),
(2, 'D3033MBTY'),
(3, '011130-01'),
(4, '011130-04C'),
(5, '021002'),
(6, '021017-B'),
(7, '021134-01'),
(8, '021135-01'),
(9, '021955-01'),
(10, '3LS91101-550'),
(11, 'D3049MBRB'),
(12, 'EF0118'),
(13, 'FV8130'),
(14, 'FY7009'),
(15, 'H05802'),
(16, 'D3033MRTE');

DECLARE @SUBSTITUTE TABLE (ID int, ITEID int, SUBSTITUTECODE varchar(100));

INSERT @SUBSTITUTE (ID, ITEID, SUBSTITUTECODE) VALUES
(5232, 1, '191045762418'),
(5442, 2, '191045762418'),
(6435, 3, '5206432380030'),
(6573, 4, '5206432380030'),
(6582, 5, '5206432357131'),
(6683, 6, '5206432369486'),
(7332, 7, '5206432380610'),
(7482, 8, '5206432380818'),
(7721, 9, '5206432346029'),
(7831, 10, '5205172116350'),
(8034, 11, '191045480992'),
(8184, 12, '4061622759543'),
(8284, 13, '4062058577497'),
(8573, 14, '4064039588089'),
(9438, 15, '4064048672519'),
(9746, 16, '191045762418');

SELECT sub.SUBSTITUTECODE
FROM @SUBSTITUTE AS sub
INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
GROUP BY sub.SUBSTITUTECODE
HAVING COUNT(sub.SUBSTITUTECODE) > 1;

我想創建一個會產生以下結果集的查詢：

換句話說，我想在表中那些記錄有重復參考的地方得到一組逗號分隔的CODEs@MATERIALSUBSTITUTECODE@SUBSTITUTE

間接地，我可以通過以下查詢找到CODE與那些重復SUBSTITUTECODEs對應的s：

SELECT prod.CODE, sub.SUBSTITUTECODE
FROM @SUBSTITUTE AS sub
INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
WHERE sub.SUBSTITUTECODE IN (SELECT sub.SUBSTITUTECODE
    FROM @SUBSTITUTE AS sub
    INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
    GROUP BY sub.SUBSTITUTECODE
    HAVING COUNT(sub.SUBSTITUTECODE) > 1)

可以在此處找到上述案例的作業小提琴。

請注意，此方案的完整案例在 SQL Server 2014 上運行。

TIA

uj5u.com熱心網友回復：

不錯的開始小提琴，謝謝！如果我們只是把你已經擁有的東西放在一個 CTE 中，我們可以圍繞它撰寫一個標準的字串聚合：

;WITH subs AS 
(
  SELECT prod.CODE, sub.SUBSTITUTECODE
  FROM @SUBSTITUTE AS sub
  INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
  WHERE sub.SUBSTITUTECODE IN (SELECT sub.SUBSTITUTECODE
    FROM @SUBSTITUTE AS sub
    INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
    GROUP BY sub.SUBSTITUTECODE
    HAVING COUNT(sub.SUBSTITUTECODE) > 1)
)
SELECT CODES = STUFF((SELECT ','   CODE 
  FROM subs AS s2 WHERE s2.SUBSTITUTECODE = subs.SUBSTITUTECODE
  FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1,1,''),
    SUBSTITUTECODE FROM subs
  GROUP BY SUBSTITUTECODE;

• 示例資料庫<>小提琴

但是我們可以稍微簡化一下這段代碼，最重要的是避免兩次參考兩個表，像這樣：

;WITH subs AS
(
  SELECT s.ITEID, s.SUBSTITUTECODE, m.CODE, 
    c = COUNT(*) OVER (PARTITION BY s.SUBSTITUTECODE)
  FROM @SUBSTITUTE AS s
  INNER JOIN @MATERIAL AS m
  ON m.ID = s.ITEID
)
SELECT CODES = STUFF((SELECT ','   CODE
  FROM subs AS s2 WHERE s2.SUBSTITUTECODE = subs.SUBSTITUTECODE
  FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'),1,1,''),
    SUBSTITUTECODE
  FROM subs 
  WHERE c > 1 
  GROUP BY SUBSTITUTECODE;

• 示例資料庫<>小提琴

請注意，在更現代的 SQL Server (2017 ) 版本上，STRING_AGG()這更容易：

SELECT CODES = STRING_AGG(m.CODE, ','), s.SUBSTITUTECODE
  FROM @SUBSTITUTE AS s
  INNER JOIN @MATERIAL AS m
  ON m.ID = s.ITEID
  GROUP BY s.SUBSTITUTECODE
  HAVING COUNT(*) > 1;

• 示例資料庫<>小提琴

uj5u.com熱心網友回復：

由于您使用的是 SQL Server 2014，因此不能使用STRING_AGG()

這是使用的解決方案 FOR XML PATH

WITH CTE AS
(
    SELECT prod.CODE, sub.SUBSTITUTECODE, 
           c = COUNT(*) OVER (PARTITION BY sub.SUBSTITUTECODE)
    FROM   @SUBSTITUTE AS sub
           INNER JOIN @MATERIAL AS prod ON prod.ID = sub.ITEID
),
CTE2 AS
(
    SELECT *
    FROM   CTE
    WHERE  c > 1
)
SELECT STUFF((SELECT ','   CODE 
              FROM CTE2 x 
              WHERE x.SUBSTITUTECODE = c.SUBSTITUTECODE 
              FOR XML PATH('')), 1, 1, ''),
       SUBSTITUTECODE
FROM   CTE2 c
GROUP BY SUBSTITUTECODE

標籤：sql-server 查询语句 sql-server-2014

上一篇：SQLServerSELECTMAX并添加1

下一篇：日期范圍內的SQL聯接事務