关联Sku/Color/Product的SQL查询如何在Limit前按Sku.price排序

你当前查询的LIMIT是直接作用在product表的子查询上，此时还未关联到sku表，自然无法使用sku.price作为排序依据。要实现LIMIT执行前按SKU价格排序，有两种常用实现方案：

方案1：调整查询结构，提前关联后分页 #

将三张表的关联逻辑移到内层子查询，排序、分页都在内层完成，外层直接取所需字段即可，修正后的SQL如下：

SELECT 
    product_id          AS anon_1_product_id,
    product_name        AS anon_1_product_name,
    product_description AS anon_1_product_description,
    product_size_chart  AS anon_1_product_size_chart,
    product_status      AS anon_1_product_status,
    color_id            AS anon_1_color_id,
    color               AS anon_1_color_color,
    product_image       AS anon_1_color_product_image,
    custom_cat_id       AS anon_1_custom_cat_id,
    back_image          AS anon_1_color_back_image,
    color_hex           AS anon_1_color_color_hex,
    sku_id              AS anon_1_sku_sku_id,
    catalog_sku_id      AS anon_1_sku_catalog_sku_id,
    cost                AS anon_1_sku_cost,
    size                AS anon_1_sku_size,
    prise               AS anon_1_sku_prise,
    in_stock            AS anon_1_sku_in_stock
FROM (
    SELECT 
        p.id                    AS product_id,
        p.name                  AS product_name,
        p.description           AS product_description,
        p.size_chart            AS product_size_chart,
        p.status                AS product_status,
        c.id                    AS color_id,
        c.color                 AS color,
        c.product_image         AS product_image,
        c.custom_cat_id         AS custom_cat_id,
        c.back_image            AS back_image,
        c.color_hex             AS color_hex,
        s.id                    AS sku_id,
        s.catalog_sku_id        AS catalog_sku_id,
        s.cost                  AS cost,
        s.size                  AS size,
        s.prise                 AS prise,
        s.in_stock              AS in_stock,
        ROW_NUMBER() OVER (ORDER BY s.prise DESC, c.id, 
            CASE s.size
                WHEN 'XS' THEN 1 WHEN 'S' THEN 2 WHEN 'M' THEN 3 WHEN 'L' THEN 4 WHEN 'XL' THEN 5
                WHEN '2XL' THEN 6 WHEN '3XL' THEN 7 WHEN '4XL' THEN 8 WHEN '5XL' THEN 9 WHEN '6XL' THEN 10
                WHEN 'YXS' THEN 11 WHEN 'YS' THEN 12 WHEN 'YM' THEN 13 WHEN 'YL' THEN 14 WHEN 'YXL' THEN 15 WHEN 'Y2XL' THEN 16
            END) AS rn
    FROM product p
    JOIN color c ON p.id = c.product_id
    JOIN sku s ON s.color_id = c.id
    WHERE lower(p.name) LIKE lower(:name_product)
) AS t
WHERE rn BETWEEN (:skip + 1) AND (:skip + :limit)
ORDER BY color_id, size;

注意：此方案分页维度是SKU，如果一个商品对应多个SKU，会按SKU数量计算分页条数。如果你需要按商品维度分页（每页返回固定数量的商品，包含其下所有颜色和SKU），请选择方案2。另外你原SQL中SKU价格字段拼写为prise，如果是笔误可自行替换为price。

方案2：拆分查询实现商品维度分页 #

拆分两次查询，逻辑更清晰，也更贴合你原有按商品分页的需求：

1. 第一步查询满足条件的商品ID，关联SKU按价格排序后取分页范围的商品ID

SELECT DISTINCT p.id AS product_id
FROM product p
JOIN color c ON p.id = c.product_id
JOIN sku s ON s.color_id = c.id
WHERE lower(p.name) LIKE lower(:name_product)
ORDER BY s.prise DESC, p.id
LIMIT :limit OFFSET :skip

2. 第二步用第一步拿到的商品ID列表，关联颜色和SKU查询完整数据，再按原有规则排序即可

SELECT 
    p.id                    AS anon_1_product_id,
    p.name                  AS anon_1_product_name,
    p.description           AS anon_1_product_description,
    p.size_chart            AS anon_1_product_size_chart,
    p.status                AS anon_1_product_status,
    c.id                    AS anon_1_color_id,
    c.color                 AS anon_1_color_color,
    c.product_image         AS anon_1_color_product_image,
    c.custom_cat_id         AS anon_1_custom_cat_id,
    c.back_image            AS anon_1_color_back_image,
    c.color_hex             AS anon_1_color_color_hex,
    s.id                    AS anon_1_sku_sku_id,
    s.catalog_sku_id        AS anon_1_sku_catalog_sku_id,
    s.cost                  AS anon_1_sku_cost,
    s.size                  AS anon_1_sku_size,
    s.prise                 AS anon_1_sku_prise,
    s.in_stock              AS anon_1_sku_in_stock
FROM product p
JOIN color c ON p.id = c.product_id
JOIN sku s ON s.color_id = c.id
WHERE p.id IN (:product_id_list)
ORDER BY c.id,
    CASE s.size
        WHEN 'XS' THEN 1 WHEN 'S' THEN 2 WHEN 'M' THEN 3 WHEN 'L' THEN 4 WHEN 'XL' THEN 5
        WHEN '2XL' THEN 6 WHEN '3XL' THEN 7 WHEN '4XL' THEN 8 WHEN '5XL' THEN 9 WHEN '6XL' THEN 10
        WHEN 'YXS' THEN 11 WHEN 'YS' THEN 12 WHEN 'YM' THEN 13 WHEN 'YL' THEN 14 WHEN 'YXL' THEN 15 WHEN 'Y2XL' THEN 16
    END

此方案分页逻辑和详情查询逻辑分离，避免单SQL过于复杂，也保证了分页维度是商品，不会因为单个商品多SKU导致分页计数异常。

内容的提问来源于stack exchange，提问作者Іван Юкіш