我有这样的样本数据
Id UserId EventDate EventId EventTime Fg RN
1 1 2018-10-15 6 2018-10-15 12:10:10.000 0 8
2 1 2018-10-15 6 2018-10-15 12:10:11.000 0 7
3 1 2018-10-15 6 2018-10-15 12:10:12.000 1 6
4 1 2018-10-15 6 2018-10-15 12:10:13.000 1 5
5 1 2018-10-15 6 2018-10-15 12:10:15.000 0 4
6 1 2018-10-15 6 2018-10-15 12:10:17.000 0 3
7 1 2018-10-15 6 2018-10-15 12:10:20.000 1 2
8 1 2018-10-15 6 2018-10-15 12:10:25.000 1 1
9 1 2018-10-16 8 2018-10-16 12:12:33.000 0 3
10 1 2018-10-16 8 2018-10-16 12:12:43.000 0 2
11 1 2018-10-16 8 2018-10-16 12:12:47.000 1 1
12 1 2018-10-17 9 2018-10-17 12:15:10.000 0 4
13 1 2018-10-17 9 2018-10-17 12:15:15.000 0 3
14 1 2018-10-17 9 2018-10-17 12:15:18.000 1 2
15 1 2018-10-17 9 2018-10-17 12:15:25.000 1 1
我要选择以下行
Id UserId EventDate EventId EventTime Fg RN
7 1 2018-10-15 6 2018-10-15 12:10:20.000 1 2
11 1 2018-10-16 8 2018-10-16 12:12:47.000 1 1
14 1 2018-10-17 9 2018-10-17 12:15:18.000 1 2
Fg
RN
列为1。
注册护士
列是每天每个用户标识的事件顺序,从结束向后开始。
我的解决方案是一个递归的CTE向后遍历,但这对于我拥有的数据量来说相当慢。
有哪些替代方法?
CREATE TABLE dbo.test (Id INT IDENTITY(1,1), UserId INT, EventDate DATE, EventId INT, EventTime DATETIME NOT NULL, Fg BIT, RN BIGINT)
GO
INSERT INTO dbo.Test(UserId, EventDate, EventTime, Fg, RN, EventId )
VALUES
(1, '20181015','20181015 12:10:10', 0, 8, 6)
,(1, '20181015','20181015 12:10:11', 0, 7, 6)
,(1, '20181015','20181015 12:10:12', 1, 6, 6)
,(1, '20181015','20181015 12:10:13', 1, 5, 6)
,(1, '20181015','20181015 12:10:15', 0, 4, 6)
,(1, '20181015','20181015 12:10:17', 0, 3, 6)
,(1, '20181015','20181015 12:10:20', 1, 2, 6)
,(1, '20181015','20181015 12:10:25', 1, 1, 6)
,(1, '20181016','20181016 12:12:33', 0, 3, 8)
,(1, '20181016','20181016 12:12:43', 0, 2, 8)
,(1, '20181016','20181016 12:12:47', 1, 1, 8)
,(1, '20181017','20181017 12:15:10', 0, 4, 9)
,(1, '20181017','20181017 12:15:15', 0, 3, 9)
,(1, '20181017','20181017 12:15:18', 1, 2, 9)
,(1, '20181017','20181017 12:15:25', 1, 1, 9)
GO