我想为每个RoomNumber
显示Last_EmployeeId
列中的最后一个EmployeeId
。因此,当有一个NULL
值时,我应该返回EmployeeId
而不是NULL
。下面的查询几乎达到了Partition by value_partition
的效果,因为value_partition
为非NULL记录显示了不同的数字。然而,在某一点上,value_partition
显示的NULL和非NULL记录的数字完全相同(在3000条记录之后(。因此,它还为所有不相关的房间返回EmployeeId
(如您在下面粘贴的数据片段中所见(。
我使用以下代码:
;WITH Rooms_Rank AS (
SELECT
Rooms.*
,ROW_NUMBER() OVER (PARTITION BY Rooms.RoomNumber ORDER BY Rooms.[Date]) -
ROW_NUMBER() OVER (PARTITION BY Rooms.RoomNumber, Rooms.beginDate ORDER BY Rooms.[Date]) AS Rnk--[Services].beginDate ORDER BY RoomDate.[Date]) AS Rnk
FROM Rooms
)
SELECT
[Date]
,RoomNumber
,EmployeeId
,value_partition
,first_value(EmployeeId) OVER (PARTITION BY value_partition ORDER BY [Date]) AS Last_EmployeeId
FROM (
SELECT *,
SUM(CASE WHEN EmployeeId is null THEN 0 ELSE 1 END) OVER (ORDER BY RoomNumber, CAST([Date] AS DATE)) AS value_partition
FROM Rooms_Rank
) AS q
ORDER BY [Date] ASC, RoomNumber
数据集的小样本(因为这个问题只在记录3000+之后出现(
日期 | 房间编号 | 员工IDvalue_partition | >Last_EmplateID | |
---|---|---|---|---|
2020年10月12日 | 33 | >133 | 607 | |
2020年10月12日 | 34 | NULL | 136 | NULL |
2020年10月12日 | 401 | 空 | 136 | NULL |
2020年10月12日 | 71 | 空 | 223空||
2020-10-13 | 33 | >607 | 134 | 607|
2020年10月13日 | 34 | 空 | 136 | 空 |
2020年10月13日 | 401 | 空 | 136 | NULL |
2020-10-13 | 71 | 空 | 223 | 空|
2020年10月14日 | 33 | 607135 | >607 | |
2020年10月14日 | 34 | 空 | 136 | 空|
2020-10-14 | 401 | 空 | 136 | NULL |
2020年10月14日 | 71 | 空 | 223 | 空|
2020-10-15 | 33 | >607 | 136 | 607|
2020-10-15 | 34 | 空 | 136 | 607 |
2020年10月15日 | 401 | 空 | 136 | 607 |
2020-10-15 | 71 | 空 | 223 | 空|
2020-10-16 | 33 | 空136 | >607 | |
2020年10月16日 | 34 | 空 | 136 | 607 |
2020-10-16 | 401 | 空 | 136 | 607 |
2020-10-16 | 71 | 空 | 223 | 空
我知道你想要最后一个非null
employeeid
,按房间计算。如果SQL Server支持窗口函数lag()
的选项ignore nulls
,那么这将是非常困难的——唉,很少有数据库支持,SQL Server也不是其中之一。
相反,我们可以将其作为一个缺口和岛屿问题来解决。我们可以使用非null值的累积计数将记录分组,然后为每个组选择唯一的非null值:
select t.*,
max(employeeid) over(partition by roomnumber, grp order by date) as lastemployeeid
from (
select rr.*,
count(employeeid) over(partition by roomnumber order by date) grp
from rooms_rank rr
) rr