如何在每列中选择下一个非空值。T-SQL

Damien Barker 发表于 Dev

达米安·巴克（Damien Barker）

我试图根据日期值选择每列的最后一个非空值。

我有一张看起来像这样的桌子-

Email           Name1   Name2   Job     Date
[email protected]  Ron     NULL    NULL    2015-01-01 00:00:00.000
[email protected]  Dave    Smith   NULL    2014-01-01 00:00:00.000
[email protected]  NULL    NULL    NULL    2013-01-01 00:00:00.000
[email protected]  NULL    Smith   NULL    2014-01-01 00:00:00.000
[email protected]  NULL    Ford    Plumber 2015-01-01 00:00:00.000`

我想为每个电子邮件地址的每一列显示最新的非空值。

输出应为-

Email           Name1   Name2   Job
[email protected]  Ron     Smith   NULL
[email protected]  NULL    Ford    Plumber

我已经写了一些难看的SQL来解决此问题，但是我想将此逻辑应用于具有更多列的另一个表。

我的问题是-是否有一种更简单的方法而不必为每个列联接？

目前的解决方案如下-

select distinct  a.[Email],b.[Name1],c.[Name2],d.[job] from 
(
select [Email] from #test
)
A
left join 
(
SELECT [Email],
 FIRST_VALUE([Name1]) over(partition by [Email] order by [Date] desc) as [Name1]
from #test
where [Name1] is not null
) b
on a.[Email] = b.[Email]
left join 
(
SELECT [Email],
FIRST_VALUE([Name2]) over(partition by [Email] order by [Date] desc) as [Name2]
from  #test
where [Name2] is not null
) c
on a.[Email] = c.[Email]
left join 
(
select [Email],
FIRST_VALUE([Job]) over(partition by [Email] order by [Date] desc) as [Job]
from #test
where  [Job] is not null
) d
on a.[Email] = d.[Email]

如果有帮助，这是示例表的DDL / DML-

create table #test
([Email] nvarchar(50),
[Name1]  nvarchar(50),
[Name2] nvarchar(50),
[Job] nvarchar(50),
[Date] datetime)

insert into #test
values
('[email protected]', 'Ron', null,null,'20150101'),
('[email protected]', 'Dave' ,'Smith',null, '20140101'),
('[email protected]', null, null, null ,'20130101'),
('[email protected]', null, 'Smith', null, '20140101'),
('[email protected]', null, 'Ford', 'Plumber','20150101')

戈登·利诺夫（Gordon Linoff）

有些方法不需要那么多的联接。没有一个很简单，因为SQL Server不支持ignore nullson上的选项lag()。

基本上，您需要在每一列上进行逻辑运算。没有子查询的一种方法是：

select distinct email,
       first_value(name1) over (partition by email
                                order by (case when name1 is not null then date else '2000-01-01' end) desc
                               ) as name1,
       . . .
from #test;

一种替代方法是使用外部应用：

select t.email, name1, . . .
from (select distinct email from #test t) t outer apply
     (select top 1 name1
      from #test t2
      where t2.email = t.email and name1 is not null
      order by date desc
     ) name1 . . .

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。