sql - SQL Server SELECT STUFF 返回不正确的值
问题描述
我过去使用过 STUFF,没有任何问题,但这个有点不同。也许是由于表结构。如果有人想看一下,我已经设置了一些不错的测试脚本。任何输入表示赞赏。
DECLARE @DetailTbl TABLE(DetailID INT IDENTITY(1,1) NOT NULL, Roll VARCHAR(6))
INSERT INTO @DetailTbl (Roll) VALUES ('ABC')
INSERT INTO @DetailTbl (Roll) VALUES ('DEF')
INSERT INTO @DetailTbl (Roll) VALUES ('JKL')
INSERT INTO @DetailTbl (Roll) VALUES ('MNO')
DECLARE @RunTbl TABLE(ID INT IDENTITY(1,1) NOT NULL, Run VARCHAR(6))
INSERT INTO @RunTbl (Run) VALUES ('X12')
INSERT INTO @RunTbl (Run) VALUES ('Y34')
INSERT INTO @RunTbl (Run) VALUES ('Z56')
DECLARE @RunRollRef TABLE(ID INT IDENTITY(1,1) NOT NULL, RunID INT, DetailID INT)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (1, 1)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (1, 2)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (1, 3)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (2, 4)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (2, 1)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (3, 2)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (3, 3)
SELECT rt.ID,
(SELECT
STUFF((SELECT dt.Roll + ','
FROM @DetailTbl dt
INNER JOIN @RunRollRef ref ON ref.DetailID = dt.DetailID
WHERE dt.DetailID = r.DetailID
FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'), 1, 0, ''))
AS Rolls
FROM @RunTbl rt
INNER JOIN @RunRollRef r ON r.RunID = rt.ID
--Expected Output
--1 ABC,DEF,JKL,
--2 MNO,ABC,
--3 DEF,JKL,
解决方案
我会使用APPLY
:
select rt.ID, stuff(tt.Rolls, 1, 1, '') as Rolls
from @RunTbl rt outer apply
( select ', '+dt.Roll
from @RunRollRef rf inner join
@DetailTbl dt
on dt.DetailID = rf.DetailID
where rf.RunID = rt.ID
for xml path('')
) tt(Rolls);
外部select
有不必要JOIN
的 s,这实际上是不需要的。
您需要引用ref.RunID = rt.ID
而不是dt.DetailID = r.DetailID
在子查询中。