SQL Server STUFF不适用于NULL值和分组

我有下面的架构和数据表。

CREATE TABLE [dbo].[SearchTest](
    [DocumentNumber] [int] NOT NULL,
    [AlphaNumeric] [nvarchar](50) NULL,
    [Integers] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) VALUES (1, N'abc', 1)
GO
INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) VALUES (2, N'abc', 1)
GO
INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) VALUES (3, N'bcd', 2)
GO
INSERT [dbo].[SearchTest] ([DocumentNumber], [AlphaNumeric], [Integers]) VALUES (4, N'bcd', 2)
GO

表格数据:

SearchTest

I would like to do grouping using Alphanumeric and Integers column and get the DocumentNumber as comma separated value in my final result.

我的最终结果应该像这样,

Final Result

这是给出上述输出的查询,

SELECT * FROM
    (select STUFF((SELECT ', ' + CAST(DocumentNumber AS VARCHAR(10)) [text()] 
            FROM SearchTest 
            Where AlphaNumeric = Result.Alphanumeric OR Integers = Result.Integers 
            FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') DocumentNumbers, 
            COUNT(DocumentNumber) TotalDocuments, 
            Result.AlphaNumeric, 
            Result.Integers 
            from 
        (select * from SearchTest Where AlphaNumeric LIKE '%b%' OR Integers = 1) 
     as Result group by Result.AlphaNumeric, Result.Integers) 
as Final

However the above query breaks if I have null values in Integers column.

例如,

If I have NULL value in my integer Columns as shown below,

Search Test With NULL Values

现在我的查询中断,在我的东西查询中得到错误的结果,如下所示

enter image description here

Grouping works fine in the above query but STUFF part which gives DocumentNumbers gives wrong result. In this case it has be 2 in first row and 1 in second row.

请协助我出问题了

评论
cet
cet

You need to change the WHERE clause of the inner query to a) use AND instead of OR and b) to check for NULLs too.

SELECT stuff((SELECT concat(', ', documentnumber)
                     FROM searchtest st2
                     WHERE (st2.alphanumeric = st1.alphanumeric
                             OR st2.alphanumeric IS NULL
                                AND st1.alphanumeric IS NULL)
                           AND (st2.integers = st1.integers
                                 OR st2.integers IS NULL
                                    AND st1.integers IS NULL)
                     FOR XML PATH('')),
             1,
             2,
             '') documentnumbers,
      count(*) totaldocuments,
      alphanumeric,
      integers
      FROM searchtest st1
      WHERE st1.alphanumeric LIKE '%b%'
             OR st1.integers = 1
      GROUP BY st1.alphanumeric,
               st1.integers;
点赞
评论