SQL如何按用户统计最早预约日期并新增布尔标识列

实现方案（支持窗口函数的数据库，如MySQL 8.0+、PostgreSQL、SQL Server等） #

直接在现有查询的基础上，用MIN()窗口函数按用户id分组取最小预约创建时间，再和当前行的时间对比即可，修改后SQL如下：

SELECT 
    id, 
    email, 
    voucher_id, 
    created_at_reservation, 
    reservation_id,
    -- 新增布尔列判断当前行是否为用户最早的已支付预约
    created_at_reservation = MIN(created_at_reservation) OVER (PARTITION BY id) AS is_first_reservation
FROM
    (SELECT *
     FROM users
     LEFT JOIN 
         (SELECT 
              status, user_id, voucher_id, 
              created_at AS created_at_reservation, id AS reservation_id
          FROM 
              reservations 
          WHERE 
              reservations.status = 'paid') AS r ON r.user_id = users.id) AS u

逻辑说明 #

• 用MIN(created_at_reservation) OVER (PARTITION BY id)窗口函数，按用户id（即外层查询的u.id）分组，计算每个用户所有已支付预约的最小创建时间
• 直接将当前行的created_at_reservation和这个最小值做等值判断，得到的结果就是需要的布尔值：如果是最小值返回TRUE，否则返回FALSE；如果用户没有已支付预约（created_at_reservation为NULL），判断结果也会是NULL，可按需用COALESCE转成FALSE

兼容老版本MySQL（不支持窗口函数）的实现方案 #

如果数据库不支持窗口函数，可以用关联子查询的方式实现：

SELECT 
    u.id, 
    u.email, 
    u.voucher_id, 
    u.created_at_reservation, 
    u.reservation_id,
    u.created_at_reservation = (
        SELECT MIN(created_at) 
        FROM reservations 
        WHERE status = 'paid' AND user_id = u.id
    ) AS is_first_reservation
FROM
    (SELECT *
     FROM users
     LEFT JOIN 
         (SELECT 
              status, user_id, voucher_id, 
              created_at AS created_at_reservation, id AS reservation_id
          FROM 
              reservations 
          WHERE 
              reservations.status = 'paid') AS r ON r.user_id = users.id) AS u

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