如何修改MySQL查询实现按baseModel字段分组且每个值仅返回1条数据

要实现每个baseModel仅返回一条数据，你需要调整分组逻辑，同时明确同baseModel下多条产品的筛选规则，这里我们沿用你原查询的排序规则（sort_order升序、产品名称升序），取每个分组下排序最靠前的1条数据即可。

MySQL 8.0及以上版本方案（推荐） #

使用ROW_NUMBER()窗口函数实现分组排序筛选，修改后SQL如下：

SELECT * FROM (
    SELECT
        p.baseModel, 
        p.product_id,
        (
            SELECT AVG(rating) total
            FROM `oc_review` r1
            WHERE
                r1.product_id = p.product_id
                AND r1.status = '1'
            GROUP BY r1.product_id
        ) rating,
        (
            SELECT price
            FROM `oc_product_discount` pd2
            WHERE
                pd2.product_id = p.product_id
                AND pd2.customer_group_id = '1'
                AND pd2.quantity = '1'
                AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW())
                AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW()))
            ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1
        ) discount,
        (
            SELECT price
            FROM `oc_product_special` ps
            WHERE
                ps.product_id = p.product_id
                AND ps.customer_group_id = '1'
                AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW())
                AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))
            ORDER BY ps.priority ASC, ps.price ASC LIMIT 1
        ) special,
        p.viewed,
        p.sort_order,
        pd.name,
        -- 给同baseModel的产品按原规则排序编号
        ROW_NUMBER() OVER (PARTITION BY p.baseModel ORDER BY p.sort_order ASC, LCASE(pd.name) ASC) AS rn
    FROM `oc_product` p 
    LEFT JOIN `oc_product_to_category` p2c ON (p2c.product_id = p.product_id) 
    LEFT JOIN `oc_category_path` cp ON (cp.category_id = p2c.category_id)
    LEFT JOIN `oc_product_description` pd ON (p.product_id = pd.product_id)
    LEFT JOIN `oc_product_to_store` p2s ON (p.product_id = p2s.product_id)
    WHERE
        p.status = '1'
        AND p.date_available <= NOW()
        AND p2s.store_id = '0'
        AND pd.language_id = '2'
        AND cp.path_id = '291'
) t 
WHERE rn = 1 -- 每个baseModel只取排序第一的条目
LIMIT 0, 36

MySQL 5.x 版本兼容方案 #

如果使用的是不支持窗口函数的老版本MySQL，可以用分组关联的方式实现：

SELECT
    p.baseModel, 
    p.product_id,
    (
        SELECT AVG(rating) total
        FROM `oc_review` r1
        WHERE
            r1.product_id = p.product_id
            AND r1.status = '1'
        GROUP BY r1.product_id
    ) rating,
    (
        SELECT price
        FROM `oc_product_discount` pd2
        WHERE
            pd2.product_id = p.product_id
            AND pd2.customer_group_id = '1'
            AND pd2.quantity = '1'
            AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW())
            AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW()))
        ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1
    ) discount,
    (
        SELECT price
        FROM `oc_product_special` ps
        WHERE
            ps.product_id = p.product_id
            AND ps.customer_group_id = '1'
            AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW())
            AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW()))
        ORDER BY ps.priority ASC, ps.price ASC LIMIT 1
    ) special,
    p.viewed
FROM `oc_product` p 
LEFT JOIN `oc_product_to_category` p2c ON (p2c.product_id = p.product_id) 
LEFT JOIN `oc_category_path` cp ON (cp.category_id = p2c.category_id)
LEFT JOIN `oc_product_description` pd ON (p.product_id = pd.product_id)
LEFT JOIN `oc_product_to_store` p2s ON (p.product_id = p2s.product_id)
INNER JOIN (
    -- 先查询每个baseModel下排序最靠前的product_id
    SELECT 
        p1.baseModel,
        SUBSTRING_INDEX(GROUP_CONCAT(p1.product_id ORDER BY p1.sort_order ASC, LCASE(pd1.name) ASC), ',', 1) AS target_product_id
    FROM `oc_product` p1
    LEFT JOIN `oc_product_description` pd1 ON (p1.product_id = pd1.product_id)
    WHERE
        p1.status = '1'
        AND p1.date_available <= NOW()
        AND pd1.language_id = '2'
    GROUP BY p1.baseModel
) t ON p.baseModel = t.baseModel AND p.product_id = t.target_product_id
WHERE
    p.status = '1'
    AND p.date_available <= NOW()
    AND p2s.store_id = '0'
    AND pd.language_id = '2'
    AND cp.path_id = '291'
ORDER BY p.sort_order ASC, LCASE(pd.name) ASC 
LIMIT 0, 36

注意事项 #

如果需要调整同baseModel下的产品筛选规则，比如取浏览量最高的产品，只需要修改窗口函数或者GROUP_CONCAT对应的ORDER BY条件即可。

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