SQL Server中按规则对id排名并生成指定输出表的实现疑问

需求回顾 #

现有数据已按time升序排列，需完成两个输出表的生成：

• 输出表1：对id进行排名，规则为：若同一id下的code='05'记录是表中靠前的（按time升序），则该id排名为对应顺序（示例：id为421492036的两条code='05'记录是表前两条，排名为1）
• 输出表2：提取每个id按time升序排序后的第一条记录

具体实现（SQL） #

输出表1：按id的code='05'记录最早时间排名 #

以每个id的第一条code='05'记录的time为依据，对所有id进行排名，同一id的所有记录排名一致。SQL语句如下：

SELECT 
    original.*,
    DENSE_RANK() OVER (ORDER BY first_code05_time) AS id_rank
FROM (
    SELECT 
        *,
        -- 取每个id下第一条code=05的时间
        MIN(CASE WHEN code = '05' THEN time END) OVER (PARTITION BY id) AS first_code05_time
    FROM your_table
) original
-- 若只需要包含code=05的记录，可添加WHERE条件
-- WHERE code = '05'

输出表2：提取每个id的第一条记录 #

通过窗口函数ROW_NUMBER()标记每个id下的记录顺序，筛选出第一条即可：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY time ASC) AS row_num
    FROM your_table
) t
WHERE row_num = 1

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