如何筛选每个用户首次'sold'事件及之前的所有记录？

表定义与测试数据 #

DECLARE @mytable TABLE ([userid] integer, [outcome] varchar(max), id int)  

INSERT INTO @mytable VALUES (101, 'start', 1)  
INSERT INTO @mytable VALUES (101, 'sold', 2)  
INSERT INTO @mytable VALUES (101, 'return', 3)  
INSERT INTO @mytable VALUES (101, 'sold', 4)  
INSERT INTO @mytable VALUES (102, 'start', 1)  
INSERT INTO @mytable VALUES (102, 'call', 2)  
INSERT INTO @mytable VALUES (102, 'mailer', 3)  
INSERT INTO @mytable VALUES (103, 'start', 1)  
INSERT INTO @mytable VALUES (103, 'mailer', 2)  

SELECT *  
FROM @mytable 

需求说明 #

需要查询每个userid中，首次发生sold事件的所有前置记录（包含该sold事件本身）。当前数据仅userid=101存在销售记录，期望输出结果：

101    start
101    sold

尝试过的无效SQL #

以下语句无法得到正确结果：

SELECT *  
FROM @mytable 
WHERE [userid] > (SELECT MAX([userid]) 
                  FROM @mytable 
                  WHERE [outcome] = 'sold') 

SELECT *  
FROM @mytable  
WHERE [outcome] > (SELECT MAX([outcome]) 
                   FROM @mytable 
                   WHERE [outcome] = 'sold')

解决方案 #

可以通过CTE先获取每个用户首次sold事件的最小id（id按事件顺序递增，最小id对应首次事件），再关联原表筛选符合条件的记录：

WITH FirstSold AS (
    SELECT userid, MIN(id) AS first_sold_id
    FROM @mytable
    WHERE outcome = 'sold'
    GROUP BY userid
)
SELECT t.userid, t.outcome
FROM @mytable t
JOIN FirstSold fs ON t.userid = fs.userid
WHERE t.id <= fs.first_sold_id
ORDER BY t.userid, t.id;

逻辑说明 #

1. FirstSold分组统计每个有sold记录的用户，得到他们首次sold事件的id值；
2. 将原表与FirstSold关联，筛选出该用户下id小于等于首次sold事件id的所有记录，即得到首次sold及之前的全部事件。

内容的提问来源于stack exchange，提问作者ATMA