SQL技术咨询：按冬季销量升序统计并列出产品

Let's work through this step by step to get your query right—we'll fix the gaps in your initial code and build a reliable solution for counting winter product sales sorted from lowest to highest quantity.

First, Let's Fix the Issues in Your Initial Snippet #

• You're missing a table join to connect product details from Products with their sales records in Sales.
• Using BETWEEN for a fixed date range won't work for winter, since it spans two calendar years (e.g., December 2019 to February 2020). This would miss cross-year winter sales.
• Your GROUP BY references a non-existent column (sales_products.products_id)—we need to group by the actual product identifiers from the Products table.

Correct SQL Query (MySQL Example) #

This query handles cross-year winter dates, joins tables properly, counts sales, and sorts as requested:

SELECT
    p.product_code,
    p.product_name,
    COUNT(s.sales_code) AS Quantity
FROM Products p
INNER JOIN Sales s 
    ON p.product_code = s.product_code
WHERE
    MONTH(STR_TO_DATE(s.sales_date, '%d-%m-%Y %H:%i')) IN (1, 2, 12)
GROUP BY
    p.product_code,
    p.product_name
ORDER BY
    Quantity ASC;

Breakdown of Each Part: #

1. Table Join: We use INNER JOIN to link Products (aliased as p) with Sales (aliased as s) via the shared product_code field. This ensures we only pull products that have winter sales records. If you want to include products with zero winter sales (showing 0 as Quantity), swap INNER JOIN with LEFT JOIN.
2. Date Filter:
   • STR_TO_DATE(s.sales_date, '%d-%m-%Y %H:%i') converts your dd-mm-yyyy hh:mm date string into a proper date type the database can parse.
   • MONTH(...) extracts the month number, and IN (1,2,12) targets only January, February, and December sales—regardless of the year.
3. Count Sales: COUNT(s.sales_code) tallies the number of sales records per product (assuming each sales_code represents one sale transaction).
4. Grouping: We group by product_code and product_name to aggregate sales counts for each unique product.
5. Sorting: ORDER BY Quantity ASC sorts results from the lowest to highest sales quantity.

Adjustments for Other Databases: #

• SQL Server: Replace MONTH(STR_TO_DATE(...)) with DATEPART(MONTH, CONVERT(DATE, s.sales_date, 105)) (105 is the format code for dd-mm-yyyy).
• PostgreSQL: Use EXTRACT(MONTH FROM TO_DATE(s.sales_date, 'DD-MM-YYYY HH24:MI')).

Example Output (Matching Your Expected Result): #

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