从另一个表上的另一个参考值出现两次(或更多)的表中获取以逗号分隔的值集

Question

假设SQL Server 2014中的DB设置如下:

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;

我想创建一个查询，将产生以下结果集:

tbody> <<tr>

CODES	SUBSTITUTECODE
D3033MBBY, D3033MBTY, D3033MRTE	191045762418
011130 - 01、011130 - 04 - c	5206432380030

开局不错，谢谢!如果我们只是把你已经有的东西放在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'./text()[1]', N'nvarchar(max)'),1,1,''),
SUBSTITUTECODE FROM subs
GROUP BY SUBSTITUTECODE;

• db&lt例子;在小提琴

但是我们可以稍微简化这段代码，最重要的是避免两次引用两个表，像这样:

;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'./text()[1]', N'nvarchar(max)'),1,1,''),
SUBSTITUTECODE
FROM subs 
WHERE c > 1 
GROUP BY SUBSTITUTECODE;

• db&lt例子;在小提琴

请注意，在更现代的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;

• db&lt例子;在小提琴

由于您使用的是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