sql - 删除重复项后面的行

Question

我有一个用户登录和注销标记的列表。不幸的是，登录条目后面可能并不总是跟着注销条目。
我希望删除按 [event_date] 排序时与上一行具有相同 [event] 和 [user_id] 的任何行关于如何做到这一点有什么建议吗？

示例表

CREATE TABLE #LOG (
    [id] int IDENTITY(1,1),
    [user_id] int,
    [event] varchar(50),
    [event_date] datetime
);
INSERT INTO #LOG ([user_id], [event], [event_date])
SELECT 1,'LOGIN',{ts '2010-12-15 15:31:59'}
UNION ALL SELECT 1,'LOGOUT',{ts '2010-12-15 15:32:55'}
UNION ALL SELECT 1,'LOGIN',{ts '2010-12-15 15:38:04'}
UNION ALL SELECT 1,'LOGOUT',{ts '2010-12-15 15:38:17'}
UNION ALL SELECT 1,'LOGOUT',{ts '2010-12-15 15:38:45'} -- Delete
UNION ALL SELECT 2,'LOGIN',{ts '2010-12-15 16:59:39'}
UNION ALL SELECT 2,'LOGOUT',{ts '2010-12-15 17:00:08'}
UNION ALL SELECT 2,'LOGOUT',{ts '2010-12-15 17:00:39'} -- Delete
UNION ALL SELECT 2,'LOGOUT',{ts '2010-12-15 17:01:16'} -- Delete
UNION ALL SELECT 2,'LOGIN',{ts '2010-12-15 17:01:38'}
UNION ALL SELECT 2,'LOGIN',{ts '2010-12-15 17:02:26'} -- Delete
UNION ALL SELECT 2,'LOGOUT',{ts '2010-12-15 17:02:39'}

Answer 1

;WITH T1 AS
(
SELECT * , 
        ROW_NUMBER() OVER (ORDER BY event_date)-
        ROW_NUMBER() OVER (PARTITION BY [user_id], [event] 
                               ORDER BY event_date) AS Grp
FROM #LOG
),T2 AS
(
SELECT 
   ROW_NUMBER() OVER (PARTITION BY [user_id], [event], Grp 
                          ORDER BY event_date) RN
FROM T1
)
DELETE FROM T2 
WHERE RN > 1