按group_type分组获取单条指定记录的SQL查询优化求助

你的需求核心是每个group_type分组返回一条优先级最高的记录：优先取promoted=1的条目，多个promoted=1则取最新创建的；没有promoted=1就取组内最新创建的记录。你现有的SQL已经定位到了每组的最高promoted值，但没处理同promoted下的最新记录筛选，所以会返回多条符合条件的结果。

这里给你两种实用的解决方法：

方法一：窗口函数ROW_NUMBER()（推荐，简洁易读） #

这是现代数据库（MySQL 8+、PostgreSQL、SQL Server等）都支持的方案，逻辑清晰易懂：

SELECT id, group_type
FROM (
    SELECT 
        id, 
        group_type,
        -- 按group_type分组，先按promoted降序（1优先），再按created_at降序（最新优先）
        ROW_NUMBER() OVER (
            PARTITION BY group_type 
            ORDER BY promoted DESC, created_at DESC
        ) AS row_rank
    FROM nodes
    WHERE node_id = 4321 AND status = 1
) AS ranked_records
-- 取每组的第一条（优先级最高的记录）
WHERE row_rank = 1
ORDER BY group_type;

针对你的测试数据，执行结果： #

完全符合需求：group_type=1取了promoted=1且最新的id=2；group_type=3取了promoted=1且最新的id=8。

方法二：关联子查询（兼容老版本数据库） #

如果你的数据库不支持窗口函数，可以用NOT EXISTS来筛选出每组中没有更高优先级的记录：

SELECT a.id, a.group_type
FROM nodes a
WHERE node_id = 4321 AND status = 1
AND NOT EXISTS (
    SELECT 1
    FROM nodes b
    WHERE b.node_id = a.node_id 
      AND b.status = a.status
      AND b.group_type = a.group_type
      -- 筛选出比当前记录优先级更高的：要么promoted更大，要么同promoted但创建时间更新
      AND (
          b.promoted > a.promoted
          OR (b.promoted = a.promoted AND b.created_at > a.created_at)
      )
);

这个逻辑是：对于每条记录a，检查同组里是否存在比它更符合要求的记录b，如果不存在，a就是我们要的那条目标记录。

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