如何修改SQL查询，按近30天销量排序且无销量商品后置？

Got it, let's tweak your query to get exactly the sorting behavior you want: prioritize products with sales in the last 30 days, sort those by their total sales volume in that period descending, and push products with no recent sales to the end. Here's the revised query, followed by a breakdown of the key changes:

SELECT 
    pd.products_id AS id, 
    pd.products_name AS name,
    -- Calculate total sales quantity in the last 30 days
    SUM(CASE 
        WHEN orders.sold_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) 
        THEN orders_products.products_quantity 
        ELSE 0 
    END) AS recent_30d_sales
FROM products_description pd 
LEFT JOIN products ON pd.products_id = products.products_id 
LEFT JOIN orders_products ON pd.products_id = orders_products.products_id 
LEFT JOIN orders ON orders_products.orders_id = orders.orders_id 
-- Move the orders_total filter to the JOIN clause to avoid excluding products with no orders
LEFT JOIN orders_total ON orders.orders_id = orders_total.orders_id 
    AND orders_total.class = 'ot_total'
WHERE 
    pd.c5_dataset = 'DAT' 
    AND products.products_status = 1 
    AND pd.language_id = 4 
    AND NOT EXISTS (
        SELECT 1 
        FROM products_description 
        WHERE products_id = pd.products_id AND language_id = 10
    )
GROUP BY pd.products_id, pd.products_name -- Include all non-aggregated fields for strict SQL modes
ORDER BY 
    -- First, prioritize products with any recent sales (push 0-sales to end)
    CASE WHEN recent_30d_sales > 0 THEN 0 ELSE 1 END ASC,
    -- Then sort recent sales descending
    recent_30d_sales DESC,
    -- Optional: Add a stable sort for products with no sales (e.g., by product ID)
    pd.products_id ASC;

Key Changes Explained: #

• Calculate recent 30-day sales: Added a SUM(CASE...) to compute the total quantity sold in the last 30 days. This gives us a precise value to sort by, instead of counting all-time sales as your original query did.
• Fix the orders_total join: Your original query referenced orders_total.class in the WHERE clause but didn't join the table, which would throw an error. Moving this filter to the LEFT JOIN clause ensures we don't exclude products that have no orders (since LEFT JOIN keeps all rows from the left table even if there's no match in orders_total).
• Improved sorting logic: The ORDER BY clause first splits products into two groups: those with recent sales (sorted to the top) and those without (sent to the bottom). Within the recent sales group, we sort by total sales volume descending. The final pd.products_id ASC is optional but adds consistency for products with no sales.
• Strict mode compliance: Added pd.products_name to the GROUP BY clause to avoid issues with MySQL's strict mode, which requires all non-aggregated selected fields to be included in the group by.

If you only care about whether a product has any sales in the last 30 days (not the total volume), you could replace SUM(...) with MAX(CASE WHEN orders.sold_time >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) AS has_recent_sales, and adjust the ORDER BY to has_recent_sales DESC, pd.products_id ASC — but the version above aligns better with your original intent of sorting by sales volume.

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