MSSQL按Boolean列优先级+时间戳查询TOP1数据的技术问询

核心思路 #

使用ROW_NUMBER()窗口函数结合自定义优先级排序，实现按main_dog_flag的规则选取记录，同时确保无狗记录的客户也能返回NULL值。

完整T-SQL查询 #

WITH ranked_dogs AS (
    SELECT 
        cr.customer_id,
        d.main_dog_flag,
        d.ts,
        d.dog_id,
        -- 定义优先级：1最高，0次之，NULL最低
        ROW_NUMBER() OVER (
            PARTITION BY cr.customer_id
            ORDER BY 
                CASE d.main_dog_flag 
                    WHEN 1 THEN 1 
                    WHEN 0 THEN 2 
                    ELSE 3 
                END ASC,
                d.ts DESC
        ) AS rn
    FROM customer_reserves cr
    LEFT JOIN dogs d ON cr.customer_id = d.customer_id
)
SELECT 
    customer_id,
    main_dog_flag,
    ts,
    dog_id
FROM ranked_dogs
WHERE rn = 1;

代码解释 #

• CTE部分：通过LEFT JOIN关联客户表和狗表，保证所有客户都被包含。用CASE语句给main_dog_flag分配优先级排序值：1对应1，0对应2，NULL对应3，升序排序时优先级高的记录会排在前面。同时按ts倒序，确保同优先级下取最新的记录。ROW_NUMBER()按客户ID分组，给每组内的记录按规则编号。
• 主查询：筛选出每组内编号为1的记录，即为每个客户符合优先级规则的唯一狗记录。

解决的问题 #

之前仅按ts倒序的查询无法区分main_dog_flag的优先级，比如会出现main_dog_flag=0但时间更新的记录覆盖main_dog_flag=1记录的情况，本方案通过优先级排序确保高优先级记录优先被选中，即使时间更早。

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