使用版本控制选择行的最终状态

Question

我有这样的表格

ID  Value1  Value2  value3  Versioning
1   sport   tennis    2         1
1   NULL    NULL      4         2
1   NULL    football NULL       3
1   game    NULL     NULL       4

这实际上是一个从一个数据库复制到另一个数据库的自定义复制表。逻辑如下： 您复制的第一行 (versioning=1( 附带其所有字段 。然后，每次对原始表进行任何更新时，仅复制更改的值，而不是整个表。因此，经过 4 次版本控制后，我们最终会像上面一样。我需要做的是创建一个可以读取此表的查询，并且只返回一行实际上是最后一个状态。 使用我们的示例表，我期望的结果将是

ID Value1 Value2      Value3
1   game   football     4

解释 Value1 int 在第一个版本的结果时，我在第 2 版和第 3 版中有"运动"，我们没有任何变化，在第 4 版中它已更新为"游戏"。分别对于其他值，我们有网球 -> 无变化 -> 足球 -> 没有变化，对于值 3，我们有 2 -> 4-> 没有变化 -> 每个 -> 代表一个版本没有变化。

Answer 1

为此，您可以将版本控制和值合并到单个二进制列中，然后选择最大值。最短形式的查询是：

SELECT  t.ID,
Value1 = CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
+ CONVERT(VARBINARY(50), t.Value1)), 5, 50)),
Value2 = CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
+ CONVERT(VARBINARY(50), t.Value2)), 5, 50)),
Value3 = CONVERT(INT, SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
+ CONVERT(VARBINARY(50), t.Value3)), 5, 50))
FROM    YourTable AS t
GROUP BY ID;

为了解释正在发生的事情，我将只关注值 3，并减少样本数据。

该过程的第一步只是将排序列和值列合并为单个二进制值：

SELECT  *,
BinaryValue3 = CONVERT(BINARY(2), t.Versioning)  + CONVERT(BINARY(2), t.Value3)     
FROM    (VALUES (1, 2, 1), (1, 4, 2), (1, NULL, 3)) AS t (ID, Value3, Versioning)

这给了：

ID      Value3  Versioning      BinaryValue3
--------------------------------------
1       2           1           0x00010002
1       4           2           0x00020004
1       NULL        3           NULL

然后我们取二进制值的最大值。这依赖于两件事：

1. 连接 NULL 将产生 NULL，因此非 NULL 记录只有一个二进制值
2. 由于二进制值将从左到右排序，因此MAX函数将始终选取具有最高版本数的二进制值

然后一旦我们有了我们的最大二进制值(0x00020004(，它只是一个提取右侧的情况，并将其转换回原始数据类型。

完整工作演示

DECLARE @T TABLE 
(
ID INT NOT NULL, 
Value1  VARCHAR(50), 
Value2 VARCHAR(50), 
value3 INT, 
Versioning INT NOT NULL,
PRIMARY KEY (ID, Versioning)
);
INSERT @T (ID, Value1, Value2, Value3, Versioning)
VALUES
(1, 'sport', 'tennis', 2, 1),
(1, NULL, NULL, 4, 2),
(1, NULL, 'football', NULL, 3),
(1, 'game', NULL, NULL, 4);
SELECT  t.ID,
Value1 = CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
+ CONVERT(VARBINARY(50), t.Value1)), 5, 50)),
Value2 = CONVERT(VARCHAR(50), SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
+ CONVERT(VARBINARY(50), t.Value2)), 5, 50)),
Value3 = CONVERT(INT, SUBSTRING(MAX(CONVERT(BINARY(4), t.Versioning) 
+ CONVERT(VARBINARY(50), t.Value3)), 5, 50))
FROM    @T AS t
GROUP BY ID;

您还可以将此方法与窗口函数一起使用，将最后一个非空值添加到每一行，因此，如果要填写所有空值，可以使用最后一个非空值，您可以：

DECLARE @T TABLE 
(
ID INT NOT NULL, 
Value1  VARCHAR(50), 
Value2 VARCHAR(50), 
value3 INT, 
Versioning INT NOT NULL,
PRIMARY KEY (ID, Versioning)
);
INSERT @T (ID, Value1, Value2, Value3, Versioning)
VALUES
(1, 'sport', 'tennis', 2, 1),
(1, NULL, NULL, 4, 2),
(1, NULL, 'football', NULL, 3),
(1, 'game', NULL, NULL, 4);
SELECT  t.ID,
ActualValue1 = t.Value1,
ActualValue2 = t.Value2,
ActualValue3 = t.Value3,
LastNonNUllValue1 = CONVERT(VARCHAR(50), SUBSTRING(MAX(Value1Bin) OVER(PARTITION BY t.ID ORDER BY t.Versioning), 5, 50)),
LastNonNUllValue2 = CONVERT(VARCHAR(50), SUBSTRING(MAX(Value2Bin) OVER(PARTITION BY t.ID ORDER BY t.Versioning), 5, 50)),
LastNonNUllValue3 = CONVERT(INT, SUBSTRING(MAX(Value3Bin) OVER(PARTITION BY t.ID ORDER BY t.Versioning), 5, 50)),
t.Versioning
FROM    @T AS t
CROSS APPLY
(   SELECT  Value1Bin = CONVERT(BINARY(4), t.Versioning) + CONVERT(VARBINARY(50), t.Value1),
Value2Bin = CONVERT(BINARY(4), t.Versioning) + CONVERT(VARBINARY(50), t.Value2),
Value3Bin = CONVERT(BINARY(4), t.Versioning) + CONVERT(VARBINARY(50), t.Value3)
) AS b
ORDER BY t.Versioning;

这给了：

ID  ActualValue1    ActualValue2    ActualValue3    LastNonNUllValue1   LastNonNUllValue2   LastNonNUllValue3   Versioning
------------------------------------------------------------------------------------------------------------------------------
1   sport           tennis          2               sport               tennis                  2                   1
1   NULL            NULL            4               sport               tennis                  4                   2
1   NULL            football        NULL            sport               football                4                   3
1   game            NULL            NULL            game                football                4                   4

欲了解更多信息，请参阅Itzik Ben-Gan的The Last non NULL Puzzle

Answer 2

这在SQL Server中相当棘手，因为它不支持窗口函数上的ignore null选项。 您可以使用重复的apply，每列一个：

select t.id, t1.value1, t2.value2, t3.value3
from (values (1)) t(id) outer apply
(select top (1) t2.value1
from yourtable t1
where t1.id = t.id and t1.value1 is not null
order by t1.versioning desc
) t1 outer apply 
(select top (1) t2.value1
from yourtable t2
where t2.id = t.id and t2.value1 is not null
order by t2.versioning desc
) t2 outer apply 
(select top (1) t3.value1
from yourtable t3
where t3.id = t.id and t3.value1 is not null
order by t3.versioning desc
) t3;

Answer 3

使用一个CTE返回[ValueX]的每一列的最新非空值的[Versioning]，然后连接到表：

with cte as (
select [ID],
max(case when [Value1] is not null then [Versioning] end) v1,
max(case when [Value2] is not null then [Versioning] end) v2,
max(case when [Value3] is not null then [Versioning] end) v3
from tablename
group by [ID]
)
select c.[ID], t1.[Value1], t2.[Value2], t3.[Value3]
from cte c
inner join tablename t1 on t1.[ID] = c.[ID] and t1.[Versioning] = c.v1
inner join tablename t2 on t2.[ID] = c.[ID] and t2.[Versioning] = c.v2
inner join tablename t3 on t3.[ID] = c.[ID] and t3.[Versioning] = c.v3

请参阅演示。
结果：

> ID | Value1 | Value2   | Value3
> -: | :----- | :------- | :-----
>  1 | game   | football | 4     

Answer 4

还有一个选择。

在这里，我们取消透视您的数据，然后透视

例

Select *
From  (
Select Top 1 with ties 
A.ID
,B.*
From  YourTable A
Cross Apply ( values ('Value1',Value1)
,('Value2',Value2)
,('Value3',convert(varchar(50),Value3))
) B(Item,Value)
Where Value is not null
Order By row_number() over (partition by id,item order by versioning desc)
) pvt
Pivot (max(value) for item in ([Value1],[Value2],[Value3]) ) pvt

返回

ID  Value1  Value2      Value3
1   game    football    4

Answer 5

假设您的表是"tablename"，并且以下代码给出了最新值，并且可以扩展到任何其他列

select 
(SELECT TOP 1 Value1 FROM tablename WHERE Value1 IS NOT NULL ORDER BY Versioning desc) Value1,
(SELECT TOP 1 Value2 FROM tablename WHERE Value2 IS NOT NULL ORDER BY Versioning desc) Value2,
(SELECT TOP 1 Value3 FROM tablename WHERE Value3 IS NOT NULL ORDER BY Versioning desc) Value3