sql - 查询列表中和不在列表中的项目

Question

我有一个表“tbl_Items”，其中包含以下各列

    [Id] [int] NULL,
    [ItemNo] [varchar](50) NULL,
    [TotalPieces] [int] NULL

另一个表“tbl_ItemPieces”包含以下列

    [Id] [int] NULL,
    [ItemId] [int] NULL,
    [PieceNo] [int] NULL

示例值如下:

tbl_Items

Id  ItemNo  TotalPieces
1   1001    5
2   1002    3
3   1003    4

tbl_ItemPieces

Id  ItemId  PieceNo
1   1       1
2   1       2
3   2       1
4   2       3
5   3       3
6   3       4

我使用下面的查询来获取可用件数和总件数以及可用件数(以逗号分隔的字符串形式)。

    select 
        a.ItemNo, COUNT(b.PieceNo) ActualPieces, a.TotalPieces,    

        STUFF((SELECT ', ' + CAST( PieceNo as varchar(50))
        FROM tbl_ItemPieces b 
        WHERE b.itemId = a.Id
        FOR XML PATH('')), 1, 2, '')    

    from tbl_Items a
    inner join tbl_ItemPieces b
    on a.Id = b.itemId
    group by a.ItemNo, a.TotalPieces, a.Id

结果如下

ItemNo  ActualPieces    TotalPieces AvailablePieces
1001    2                  5            1, 2
1002    2                  3            1, 3
1003    2                  4            3, 4

我想要另一列作为逗号分隔的字符串，其中包含表格中没有的件数，假设如果总数为 5 并且包含的​​件数为 1,3，则此列值为“2,4,5”

预期结果

ItemNo  ActualPieces    TotalPieces  AvailablePieces NotAvailablePieces
1001    2               5            1, 2            3,4,5
1002    2               3            1, 3            2
1003    2               4            3, 4            1,2

Answer 1

下面的解决方案使用递归 cte (cte_AllPieceNo) 为每个项目生成可能的 PieceNo 列表

从那里，只需使用它并检查 tbl_ItemPieces 中的 NOT EXISTS()

; with
cte_AllPieceNo as                 -- Added this
(
    select  Id, TotalPieces, PieceNo = 1
    from    tbl_Items
    union all
    select  Id, TotalPieces, PieceNo = PieceNo + 1
    from    cte_AllPieceNo
    where   PieceNo < TotalPieces
)
SELECT   
    a.ItemNo, 
    COUNT(b.PieceNo) ActualPieces, 
    a.TotalPieces,
    STUFF(( SELECT  ', ' + CAST( PieceNo as varchar(50) )
            FROM    tbl_ItemPieces b 
            WHERE   b.ItemId = a.Id
            FOR XML PATH('')), 1, 2, '') as AvailablePieces,
    STUFF(( SELECT  ', ' + CAST( c.PieceNo as varchar(50) ) -- added this
            FROM    cte_AllPieceNo c
            WHERE   c.Id    = a.Id
            AND     NOT EXISTS
                    (
                        SELECT  *
                        FROM    tbl_ItemPieces d
                        WHERE   d.ItemId    = c.Id 
                        AND     d.PieceNo   = c.PieceNo
                    )
            FOR XML PATH('')),1,2, '') as NotAvailablePieces
FROM    tbl_Items a
        INNER JOIN tbl_ItemPieces b on a.Id = b.ItemId
GROUP BY a.ItemNo, 
         a.TotalPieces, 
         a.Id

如果您有统计表，则可以使用它来替换递归cte

这是使用计数表的代码部分。

cte_AllPieceNo as
(
    select  Id, PieceNo = n
    from    tbl_Items
            cross join tally
    where   n >= 1
    and     n <= TotalPieces
)