MySQL查询：如何用GROUP BY和ORDER BY获取每个product_id的指定行

方法一：使用窗口函数（MySQL 8.0及以上版本推荐） #

利用ROW_NUMBER()窗口函数给每个product_id分组内的行按指定规则编号，直接取编号为1的行即可，逻辑清晰且性能更优。

SELECT tmp_id, product_availability, is_available, stock_count, product_id
FROM (
    SELECT
        pa.*,
        ROW_NUMBER() OVER (
            PARTITION BY pa.product_id
            ORDER BY
                -- 先把符合可用条件的行排在最前面
                CASE WHEN pa.product_availability = 1 AND pa.is_available = 1 AND pa.stock_count >= 1 THEN 0 ELSE 1 END,
                pa.product_availability DESC,
                pa.is_available DESC,
                pa.stock_count DESC
        ) AS row_num
    FROM `product_advanced` AS `pa`
    -- 这里可以直接添加你的多表关联逻辑，比如 JOIN other_table ot ON pa.id = ot.pa_id
) AS ranked
WHERE row_num = 1;

方法二：适用于MySQL 5.x版本（无窗口函数支持） #

如果你的MySQL版本不支持窗口函数，可以用关联子查询来筛选目标行：

SELECT pa.*
FROM `product_advanced` AS pa
WHERE pa.tmp_id = (
    SELECT tmp_id
    FROM `product_advanced` AS pa2
    WHERE pa2.product_id = pa.product_id
    ORDER BY
        CASE WHEN pa2.product_availability = 1 AND pa2.is_available = 1 AND pa2.stock_count >= 1 THEN 0 ELSE 1 END,
        pa2.product_availability DESC,
        pa2.is_available DESC,
        pa2.stock_count DESC
    LIMIT 1
);

如果有复杂的多表关联，需要把相同的关联逻辑同时加到主查询和子查询中。

关键说明 #

两种方法的核心都是先按你要求的优先级排序：

• 第一优先级：标记出完全符合可用条件的行（product_availability=1、is_available=1、stock_count>=1），让这类行排在最前面
• 后续按product_availability降序、is_available降序、stock_count降序排序，确保没有可用产品时取相对最优的行，无符合条件时取第一行

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