SQL查询按条款分组

debugcn 发表于 Dev

拉胡尔·阿格瓦尔（Rahul Aggarwal）

我面临SQL查询问题。这是我的表结构：

DECLARE @tab TABLE
             (
                   Id INT IDENTITY, 
                   Rid INT, 
                   Address1 VARCHAR(50),
                   City VARCHAR(20)
              ) 

INSERT INTO @tab VALUES (56, 'Test1', 'New York')
INSERT INTO @tab VALUES (1253, 'Test1', 'Delhi')
INSERT INTO @tab VALUES (56, 'Address5', 'Cali')
INSERT INTO @tab VALUES (1253, 'Address5', 'Delhi')

SELECT * FROM @tab

我的查询：

SELECT
    Address1, STUFF((SELECT ',' + CONVERT(Varchar, Id)
                     FROM @tab TR
                     WHERE TR.Rid IN (56, 1253)
                     GROUP BY Id, Rid, Address1 
                     FOR XML PATH('')), 1, 1, '') AS addid
FROM
    @tab T
WHERE 
    T.Rid IN (56,1253)
GROUP BY
    T.Address1

它向我显示了所有逗号形式的Ids，而我想像在第一列和第二栏中Address一样显示Ids明智，即，Ids应该按Address1分组。1,23,4

谢谢

丰

您需要过滤TR.Address1=T.Address1而不是TR.Rid IN (56, 1253)

SELECT
    Address1, STUFF((SELECT ',' + CONVERT(Varchar, Id)
                     FROM @tab TR
                     WHERE TR.Address1 = T.Address1 // Adjust the condition here
                     GROUP BY Id, Rid, Address1 
                     FOR XML PATH('')), 1, 1, '') AS addid
FROM
    @tab T
WHERE 
    T.Rid IN (56,1253)
GROUP BY
    T.Address1

现场演示