sql-server - 仅最大空值并将其他 SQL Server 分组

Question

我有一张这样的桌子：

Department  |Jan |Feb |Mar | Apr| 
+-----------+----+----+----+----+
|A          | 1  |NULL|NULL|NULL|    
|A          |NULL|NULL|2   |NULL|    
|A          |NULL|NULL|NULL|7   |    
|B          | 1  |NULL|2   |NULL|    
|B          |NULL|NULL|5   |NULL|    
|B          |NULL|6   |NULL|NULL| 

我希望输出是：

+-----------+----+----+----+----+
|Department |Jan |Feb |Mar | Apr| 
+-----------+----+----+----+----+
|A          | 1  |NULL|2   |7   |    
|B          | 1  |6   |2   |NULL|    
|B          |NULL|NULL|5   |NULL|    

有人知道如何做到这一点吗？

实际上我只想对空值做最大值，然后对其余的值进行分组..

标签： sql-server

Answer 1

如果顺序并不重要（正如您在评论中所说），那么您可以使用ROW_NUMBER()窗口功能：

with cte as (
  select *,
    row_number() over (partition by [Department] order by [Jan]) rn1,
    row_number() over (partition by [Department] order by [Feb]) rn2,
    row_number() over (partition by [Department] order by [Mar]) rn3,
    row_number() over (partition by [Department] order by [Apr]) rn4
  from tablename
)
select c1.[Department], c1.[Jan], c2.[Feb], c3.[Mar], c4.[Apr]
from cte c1
inner join cte c2 on c2.[Department] = c1.[Department] and c2.rn2 = c1.rn1
inner join cte c3 on c3.[Department] = c1.[Department] and c3.rn3 = c1.rn1
inner join cte c4 on c4.[Department] = c1.[Department] and c4.rn4 = c1.rn1
where coalesce(c1.[Jan], c2.[Feb], c3.[Mar], c4.[Apr]) is not null
order by c1.[Department], c1.[Jan] desc, c2.[Feb] desc, c3.[Mar] desc, c4.[Apr] desc

请参阅演示。
结果：

> Department |  Jan |  Feb | Mar |  Apr
> :--------- | ---: | ---: | --: | ---:
> A          |    1 | null |   2 |    7
> B          |    1 |    6 |   5 | null
> B          | null | null |   2 | null