如何按自定义排序规则获取members表每个member_id对应的首行记录

分两种场景适配不同数据库版本：

方案1：支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server等） #

这是最简洁易维护的方案，直接用ROW_NUMBER()窗口函数按你的自定义规则排序后取首行：

SELECT * 
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY member_id 
            ORDER BY 
                CASE WHEN contact IS NOT NULL THEN 1 ELSE 0 END DESC,
                active_date DESC,
                id DESC
        ) AS rn
    FROM members
) t
WHERE rn = 1;

逻辑和你原来的单条查询完全一致：每个member_id分组内按你的排序规则给行编号，取编号为1的就是每个分组的首行记录。

方案2：不支持窗口函数的老版本MySQL #

可以用关联子查询的方式实现等价逻辑：

SELECT m1.*
FROM members m1
LEFT JOIN members m2 
  ON m1.member_id = m2.member_id
  AND (
    -- 先判断contact优先级：m2比m1优先级高的情况
    (CASE WHEN m2.contact IS NOT NULL THEN 1 ELSE 0 END) > (CASE WHEN m1.contact IS NOT NULL THEN 1 ELSE 0 END)
    -- contact优先级相同的情况，判断active_date
    OR ((CASE WHEN m2.contact IS NOT NULL THEN 1 ELSE 0 END) = (CASE WHEN m1.contact IS NOT NULL THEN 1 ELSE 0 END) AND m2.active_date > m1.active_date
    -- 前两个都相同的情况，判断id
    OR ((CASE WHEN m2.contact IS NOT NULL THEN 1 ELSE 0 END) = (CASE WHEN m1.contact IS NOT NULL THEN 1 ELSE 0 END) AND m2.active_date = m1.active_date AND m2.id > m1.id
  )
WHERE m2.id IS NULL;

逻辑是找到所有同member_id下没有比它优先级更高的记录的行，就是每个分组的首行。

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