我正在尝试获得另一个列,它可以在其中获取它拥有的计数的所有 id,就像如果名为 total 的计数列有 2 - id 实际上应该是那些符合 groupby 标准的 id
我尝试使用此代码,但它失败了
SELECT message,type, count(1) as total,
STUFF((
SELECT ',' + id
FROM dbo.testing md
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM testing
GROUP BY message,type
HAVING count(1) > 1;
我得到的错误:
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'FOR'.
我想你想要:
SELECT message, type, count(1) as total,
STUFF( (SELECT N',' + CONVERT(NVCHAR(MAX), id)
FROM dbo.testing t2
WHERE t2.message = t.message and t2.type = t.type
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
)
FROM testing t
GROUP BY message,type
HAVING count(1) > 1;
这修复了逻辑,但它不能解释您得到的语法错误。