多表MySQL字段排序咨询：按指定规则实现商品排序

Absolutely—we can pull this off using a mix of conditional sorting, a CTE (Common Table Expression) to calculate key product details, and CASE statements to enforce your custom ordering rules. Let’s break this down step by step.

Key Components We Need to Address: #

• Highest Available Price: For each product, we need the maximum price across all related price tables (product_price, product_wholesale_price, product_special_price).
• Custom Sort Tiers: We’ll categorize products into your three priority groups, then sort within each group by the highest price descending.

Complete SQL Query: #

WITH product_summary AS (
    SELECT
        p.id,
        p.sku,
        p.is_top,
        p.name,
        -- Calculate the highest price across all price tables
        GREATEST(
            COALESCE(MAX(pp.price), 0),
            COALESCE(MAX(pwp.price), 0),
            COALESCE(MAX(psp.price), 0)
        ) AS highest_price,
        -- Check if the product has a special price entry in product_price
        CASE WHEN EXISTS (
            SELECT 1 FROM product_price pp_special
            WHERE pp_special.product_id = p.id AND pp_special.type = 'special'
        ) THEN 1 ELSE 0 END AS has_special_price
    FROM product p
    LEFT JOIN product_price pp ON p.id = pp.product_id
    LEFT JOIN product_wholesale_price pwp ON p.id = pwp.product_id
    LEFT JOIN product_special_price psp ON p.id = psp.product_id
    GROUP BY p.id, p.sku, p.is_top, p.name
)
SELECT
    id,
    sku,
    name,
    highest_price
FROM product_summary
ORDER BY
    -- First, sort by your custom priority tiers
    CASE
        WHEN is_top = 1 AND has_special_price = 1 THEN 1
        WHEN is_top = 1 AND has_special_price = 0 THEN 2
        ELSE 3
    END ASC,
    -- Then sort by highest price descending within each tier
    highest_price DESC;

How This Works: #

1. CTE (product_summary):

   • We use LEFT JOIN to connect the product table with all three price tables, ensuring we don’t exclude products that lack entries in some price tables.
   • GREATEST() grabs the maximum price across all tables, with COALESCE() handling NULL values (replacing them with 0 so they don’t break the max calculation).
   • The EXISTS subquery checks if a product has a type='special' record in product_price—this lets us flag products for your first priority tier.

2. Sorting Logic:

   • The outer query uses a CASE statement to assign a priority number to each product:
     • Tier 1: is_top=1 + has a special price record
     • Tier 2: is_top=1 + no special price record
     • Tier 3: All other products
   • We sort by this priority number ascending (so Tier 1 comes first), then by highest_price descending to match your expected order.

Verification Against Your Sample Data: #

Running this query will return exactly the order you want:

1. Item 1 (highest_price=1.23, Tier 1)
2. Item 5 (highest_price=5.55, Tier 2)
3. Item 3 (highest_price=3.45, Tier 2)
4. Item 4 (highest_price=2.67, Tier 2)
5. Item 2 (highest_price=3.34, Tier 3)

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