获取每个列的最新值

Suppose I have the following "values" table in my SQL Server (2012) DB:

假设我的SQL Server (2012) DB中有以下“值”表:

Table1:

表1:

Id   Col1   Col2   Col3   Col4

And I want to create a second "override" table that will store values to override the original values in case a user needs to do so. So, given the table above, the override table would look as follows:

我还想创建第二个“覆盖”表，它将存储值，以便在用户需要时覆盖原始值。因此，根据上面的表，重写表将如下所示:

Overrides:

覆盖:

FK_Id   Col1   Col2   Col3   Col4   When_Inserted

Where Overrides.FK_Id references Table1.Id as a foreign key.

覆盖的地方。FK_Id参考表1。Id作为外键。

So, for example, suppose my Overrides table had the following rows within it with overrides for a row in Table1 with Id=1:

例如，假设我的重写表中有以下行，其中包含Id=1的表1中的一行的重写:

FK_Id:     Col1:             Col2:             Col3:              Col4:       When_Inserted:
1          Val1_1            Val2_1            Expected_Val3      NULL        1-Jan
1          NULL              Val2_2            NULL               NULL        2-Jan
1          NULL              Expected_Val2     NULL               NULL        3-Jan
1          Expected_Val1     NULL              NULL               NULL        4-Jan

Then, based upon the When_Inserted column - Wanting the latest inserts to take precedence, I'd want the overrides to be as follows:

然后，基于when _insert列——希望最新的插入优先，我希望重写如下所示:

FK_Id:     Col1:             Col2:             Col3:              Col4:
1          Expected_Val1     Expected_Val2     Expected_Val3      NULL

I'm trying to think of a smart way to create this SQL and am coming up with a fairly ugly solution along the lines of:

我正试图想出一种聪明的方法来创建这个SQL，我正在提出一个相当丑陋的解决方案，其思路是:

SELECT
     FK_Id
    ,(
        SELECT TOP 1
            Col1
        FROM
            Overrides O1
        WHERE
            Col1 IS NOT NULL
            AND O1.FK_Id = O.FK_Id
        ORDER BY
            O1.When_Inserted DESC
      ) Col1

      ....  <same for each of the other columns>  ....

FROM
    Overrides O
GROUP BY
    FK_Id

I'm sure there has to be a better way that is cleaner and substantially more efficient.

我相信一定有更好的方法更清洁，更有效。

3 个解决方案

#1

using a common table expression with row_number() (latest first), cross apply() to unpivot your columns, filter for the latest of each column (rn = 1), and finally pivot() back to the same form:

使用一个带有row_number()(最新的第一个)的公共表表达式，cross apply()来unpivot您的列，过滤每个列的最新数据(rn = 1)，最后pivot()返回到相同的形式:

;with cte as (
select o.fk_id, v.Col, v.Value, o.When_Inserted
  , rn = row_number() over (partition by o.fk_id, v.col order by o.when_inserted desc)
from overrides o
  cross apply (values('Col1',Col1),('Col2',Col2),('Col3',Col3),('Col4',Col4)
    ) v (Col,Value)
where v.value is not null
)
select fk_id, col1, col2, col3, col4
from (
  select fk_id, col, value
  from cte 
  where rn = 1
  ) s
pivot (max(Value) for Col in (col1,col2,col3,col4)) p

rextester demo: http://rextester.com/KGM96394

rextester演示:http://rextester.com/KGM96394

returns:

+-------+---------------+---------------+---------------+------+
| fk_id |     col1      |     col2      |     col3      | col4 |
+-------+---------------+---------------+---------------+------+
|     1 | Expected_Val1 | Expected_Val2 | Expected_Val3 | NULL |
+-------+---------------+---------------+---------------+------+

dbfiddle.uk demo comparison of 3 methods

dbfiddle。三种方法的英国demo对比

Looking at the io stats for the sample:

查看样本的io统计数据:

unpivot/pivot version:

透视/主版本:

Table 'Worktable'. Scan count 0, logical reads 0
Table 'overrides'. Scan count 1, logical reads 1

first_value over() version:

first_value /()版本:

Table 'Worktable'. Scan count 20, logical reads 100
Table 'overrides'. Scan count 1, logical reads 1

select top 1 subquery version:

选择top 1子查询版本:

Table 'overrides'. Scan count 5, logical reads 5
Table 'Worktable'. Scan count 0, logical reads 0

#2

You can use first_value():

您可以使用first_value():

select distinct fkid,
       first_value(col1) over (partition by fkid
                               order by (case when col1 is not null then 1 else 2 end),
                                        when_inserted desc
                              ) as col1,
       first_value(col2) over (partition by fkid
                               order by (case when col2 is not null then 1 else 2 end),
                                        when_inserted desc
                              ) as col2,
       . . .
from t;

The select distinct is because SQL Server does not have the equivalent functionality as an aggregation function.

select distinct是因为SQL Server不具有与聚合函数相同的功能。

#3

See my solution is quite different.

我的解决方案是完全不同的。

IMHO, my script performance will be better provided it give correct output across all sample data.

IMHO，我的脚本性能会更好，只要它能在所有示例数据中提供正确的输出。

I have use auto generated id in my script,but in case if you don't have identity id then you can use ROW_NUMBER . and my script is very easy to understand .

我在脚本中使用了自动生成的id，但是如果您没有标识id，那么可以使用ROW_NUMBER。我的剧本很容易理解。

declare @t table(id int identity(1,1),FK_Id int,Col1 varchar(50),Col2 varchar(50)
,Col3 varchar(50),Col4 varchar(50),When_Inserted date)
insert into @t VALUES
 (1 ,'Val1_1'  ,'Val2_1' ,'Expected_Val3',  NULL ,  '2017-01-1')
,(1 ,NULL     ,'Val2_2' , NULL ,  NULL,       '2017-01-2')
,(1 ,NULL     ,'Expected_Val2', NULL ,         NULL, '2017-01-3')
,(1 ,'Expected_Val1' , NULL  , NULL ,         NULL,  '2017-01-4')



;

WITH CTE
AS (
    SELECT *
        ,CASE 
            WHEN col1 IS NULL
                THEN NULL
            ELSE CONCAT (
                    cast(id AS VARCHAR(10))
                    ,'_'
                    ,col1
                    )
            END col1Code
        ,CASE 
            WHEN col2 IS NULL
                THEN NULL
            ELSE CONCAT (
                    cast(id AS VARCHAR(10))
                    ,'_'
                    ,col2
                    )
            END col2Code
        ,CASE 
            WHEN col3 IS NULL
                THEN NULL
            ELSE CONCAT (
                    cast(id AS VARCHAR(10))
                    ,'_'
                    ,col3
                    )
            END col3Code
        ,CASE 
            WHEN col4 IS NULL
                THEN NULL
            ELSE CONCAT (
                    cast(id AS VARCHAR(10))
                    ,'_'
                    ,col4
                    )
            END col4Code
    FROM @t
    )
    ,CTE1
AS (
    SELECT FK_Id
        ,max(col1Code) col1Code
        ,max(col2Code) col2Code
        ,max(col3Code) col3Code
        ,max(col4Code) col4Code
    FROM cte
    GROUP BY FK_Id
    )
SELECT FK_Id
    ,SUBSTRING(col1Code, charindex('_', col1Code) + 1, len(col1Code)) col1Code
    ,SUBSTRING(col2Code, charindex('_', col2Code) + 1, len(col2Code)) col2Code
    ,SUBSTRING(col3Code, charindex('_', col3Code) + 1, len(col2Code)) col3Code
    ,SUBSTRING(col4Code, charindex('_', col4Code) + 1, len(col4Code)) col4Code
FROM cte1 c1

#1