sql-server - 根据多列条件在同一个表中创建 "Sets"

Question

对于 BoxId 和 Revision 的每个唯一组合，其中单个 UnitTypeId 为 1，单个 UnitTypeId 为 2，两者都具有 NULL SetNumber，分配 SetNumber 为 1。

表和数据设置:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UnitTypes]') AND type in (N'U'))
Drop Table dbo.UnitTypes
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Tracking]') AND type in (N'U'))
DROP TABLE [dbo].[Tracking]
GO
CREATE TABLE dbo.UnitTypes
    (
    Id int NOT NULL,
    Notes varchar(80)
    )
GO
CREATE TABLE dbo.Tracking
    (
    Id int NOT NULL IDENTITY (1, 1),
    BoxId int NOT NULL,
    Revision int NOT NULL,
    UnitValue int NULL,
    UnitTypeId int NULL,
    SetNumber int NULL
    )
GO
ALTER TABLE dbo.Tracking ADD CONSTRAINT
    PK_Tracking PRIMARY KEY CLUSTERED 
    (
    Id
    )
GO  
Insert Into dbo.UnitTypes (Id, Notes) Values (1, 'X Coord'),
                                             (2, 'Y Coord'),
                                             (3, 'Weight'),
                                             (4, 'Length')
Go
Insert Into dbo.Tracking (BoxId, Revision, UnitValue, UnitTypeId, SetNumber)
              Values (1165, 1, 150, 1, NULL),
                     (1165, 1, 1477, 2, NULL),
                     (1165, 1, 31, 4, NULL),
                     (1166, 1, 425, 1, 1),
                     (1166, 1, 1146, 2, 1),
                     (1166, 1, 438, 1, NULL),
                     (1166, 1, 1163, 2, NULL),
                     (1167, 1, 560, 1, NULL),
                     (1167, 1, 909, 2, NULL),
                     (1167, 1, 12763, 3, NULL),
                     (1168, 1, 21, 1, NULL),
                     (1168, 1, 13109, 3, NULL)

理想的结果是:

Id  BoxId   Revision    UnitValue   UnitTypeId  SetNumber
 1   1165          1          150            1          1
 2   1165          1         1477            2          1
 3   1165          1           31            4          1
 4   1166          1          425            1          1
 5   1166          1         1146            2          1
 6   1166          1          438            1       NULL <--NULL Because there is already an existing Set
 7   1166          1         1163            2       NULL <--NULL Because there is already an existing Set
 8   1167          1          560            1          1
 9   1167          1          909            2          1
 10  1167          1        12763            3          1
 11  1168          1           21            1       NULL <--NULL Because there is not exactly one UnitTypeId of 1 and exactly one UnitTypeId of 2 for this BoxId\Revision combination.
 12  1168          1        13109            3       NULL <--NULL Because there is not exactly one UnitTypeId of 1 and exactly one UnitTypeId of 2 for this BoxId\Revision combination.

编辑:
问题是如何使用纯 TSQL 更新 SetNumber，鉴于上述约束？

Answer 1

如果我正确理解您的问题，您可以使用要求满足所有条件的子查询来执行此操作:

update  t1
set     SetNumber = 1
from    dbo.Tracking t1
where   SetNumber is null
        and 1 = 
        (
        select  case 
                when count(case when t2.UnitTypeId = 1 then 1 end) <> 1 then 0
                when count(case when t2.UnitTypeId = 2 then 1 end) <> 1 then 0
                when count(t2.SetNumber) <> 0 then 0
                else 1 
                end
        from    dbo.Tracking t2
        where   t1.BoxId = t2.BoxId
                and t1.Revision = t2.Revision
        )

count(t2.SetNumber)有点棘手:这只会计算 SetNumber 的行不是 null .所以这符合没有其他集合具有相同 (BoxId, Revision) 的标准。存在。