如何编写SQL查询按指定规则筛选订阅表的目标记录？

嘿，我来帮你搞定这个SQL查询需求！先把咱们的需求再明确下，避免走偏：

• 每个app_id优先找状态为Submitted的最早日期
• 要是这个app_id压根没提交过（没Submitted记录），就找状态是Approved或者Declined的最新日期（从你给的示例结果能看出来，这类状态取的是最后一条的日期）

下面给你两种实用的实现方式，适配不同的数据库环境：

方法一：用聚合子查询+COALESCE（简单易懂） #

这种写法逻辑直白，新手也能快速理解：

SELECT
    app_id,
    -- 先尝试取Submitted的最小日期，取不到的话就取Approved/Declined的最大日期
    COALESCE(
        (SELECT MIN(Date) FROM subscription sub WHERE sub.app_id = main.app_id AND sub.Status = 'Submitted'),
        (SELECT MAX(Date) FROM subscription sub WHERE sub.app_id = main.app_id AND sub.Status IN ('Approved', 'Declined'))
    ) AS date
FROM subscription main
GROUP BY app_id
ORDER BY app_id;

方法二：用窗口函数（推荐，扩展性强） #

如果你的数据库支持窗口函数（比如MySQL8.0+、PostgreSQL、SQL Server这些主流数据库都支持），这种写法后续调整规则会更灵活：

WITH ranked_data AS (
    SELECT
        app_id,
        Date,
        -- 给不同状态设优先级：Submitted最高（1），Approved/Declined次之（2），其他状态（比如Open）最低（3）
        CASE
            WHEN Status = 'Submitted' THEN 1
            WHEN Status IN ('Approved', 'Declined') THEN 2
            ELSE 3
        END AS status_priority,
        -- 按优先级排序，同优先级里Submitted取最早的，Approved/Declined取最晚的
        ROW_NUMBER() OVER (
            PARTITION BY app_id
            ORDER BY
                status_priority ASC,
                -- Submitted状态按日期升序，确保最早的排第一
                CASE WHEN Status = 'Submitted' THEN Date END ASC,
                -- Approved/Declined按日期降序，确保最新的排第一
                CASE WHEN Status IN ('Approved', 'Declined') THEN Date END DESC
        ) AS row_rank
    FROM subscription
)
SELECT app_id, Date AS date
FROM ranked_data
WHERE row_rank = 1
ORDER BY app_id;

结果验证 #

不管用哪种方法，跑出来的结果都会和你期望的完全一致：

app_id | date
1      | d1
2      | d4
3      | d7
4      | d9

方法二的逻辑再啰嗦两句：我们先给每条记录按状态分配优先级，然后用窗口函数给每个app_id的记录排名——优先级高的先排，同优先级里按日期规则选最符合要求的那条，最后取每个组里排名第一的记录就搞定啦。

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