如何编写SQL按月汇总Quantity生成报表？报表列需展示月份名及对应数量

Got it, let's figure out how to build that monthly Quantity count report you need! The goal is to pivot your data so each column is a month name, showing the total count of Quantity entries for that month. Below are solutions for common SQL databases, starting with a universal approach that works everywhere.

First, let's assume your table has a date field (let's call it date_column) and the Quantity field you want to count. Replace the placeholder names with your actual table and column names as you go.

Universal Approach: Conditional Aggregation #

This method works in nearly every SQL database (MySQL, PostgreSQL, SQL Server, Oracle, etc.) because it uses basic CASE statements to group counts by month.

SELECT
    -- Optional: Add grouping fields here if you need to break down counts by category/product
    -- product_id,
    -- product_name,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 1 THEN quantity END) AS January,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 2 THEN quantity END) AS February,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 3 THEN quantity END) AS March,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 4 THEN quantity END) AS April,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 5 THEN quantity END) AS May,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 6 THEN quantity END) AS June,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 7 THEN quantity END) AS July,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 8 THEN quantity END) AS August,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 9 THEN quantity END) AS September,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 10 THEN quantity END) AS October,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 11 THEN quantity END) AS November,
    COUNT(CASE WHEN EXTRACT(MONTH FROM date_column) = 12 THEN quantity END) AS December
FROM your_table_name
-- Optional: Filter to a specific year to avoid mixing data across years
-- WHERE EXTRACT(YEAR FROM date_column) = 2024
-- Optional: Group by your category fields if you added them above
-- GROUP BY product_id, product_name
;

Quick Notes: #

• If you need to sum Quantity values instead of counting entries, replace COUNT with SUM.
• For databases that use different month-extraction functions:
  • SQL Server/Oracle/MySQL: Use MONTH(date_column) instead of EXTRACT(MONTH FROM date_column)
  • PostgreSQL: You can also use TO_CHAR(date_column, 'MM') to get a 2-digit month string

SQL Server: Using Native PIVOT Syntax #

SQL Server has a built-in PIVOT operator that makes this cleaner if you prefer not to write a bunch of CASE statements:

SELECT *
FROM (
    SELECT
        -- Optional: Add grouping fields here
        -- product_id,
        DATENAME(MONTH, date_column) AS month_name,
        quantity
    FROM your_table_name
    -- Optional: Filter to a specific year
    -- WHERE YEAR(date_column) = 2024
) AS source_data
PIVOT (
    COUNT(quantity) -- Swap with SUM(quantity) if you need totals instead of counts
    FOR month_name IN (
        [January], [February], [March], [April], [May], [June],
        [July], [August], [September], [October], [November], [December]
    )
) AS pivot_table;

PostgreSQL: Using the crosstab Function #

PostgreSQL requires the tablefunc extension for pivot-style queries. First enable it, then use crosstab:

-- Enable the extension (only need to run this once per database)
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    'SELECT
        -- Optional: Add grouping fields here
        -- product_id,
        TO_CHAR(date_column, ''Month'') AS month_name,
        COUNT(quantity) AS quantity_count
    FROM your_table_name
    -- Optional: Filter to a specific year
    -- WHERE EXTRACT(YEAR FROM date_column) = 2024
    GROUP BY 
        -- Optional: Include grouping fields here
        -- product_id,
        TO_CHAR(date_column, ''Month'')
    ORDER BY EXTRACT(MONTH FROM date_column)'
) AS ct(
    -- Optional: Define grouping field types here
    -- product_id INT,
    "January" INT, "February" INT, "March" INT, "April" INT,
    "May" INT, "June" INT, "July" INT, "August" INT,
    "September" INT, "October" INT, "November" INT, "December" INT
);

MySQL: Dynamic SQL for Flexible Months #

MySQL doesn't have a native PIVOT, but you can use dynamic SQL to auto-generate month columns without hardcoding them:

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'COUNT(CASE WHEN MONTH(date_column) = ', MONTH(date_column), ' THEN quantity END) AS ', QUOTE(MONTHNAME(date_column))
        )
    ) INTO @sql
FROM your_table_name
-- Optional: Filter to a specific year
-- WHERE YEAR(date_column) = 2024;

SET @sql = CONCAT(
    'SELECT ', @sql, ' 
     FROM your_table_name 
     -- Optional: Filter year here
     -- WHERE YEAR(date_column) = 2024
     -- Optional: Add GROUP BY if using category fields
     -- GROUP BY product_id'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Final Tips: #

• If a month has no data, COUNT will return 0, but SUM will return NULL—use COALESCE(SUM(...), 0) to convert those nulls to 0 if needed.
• Always filter by year unless you intentionally want to aggregate data across multiple years into the same month columns.

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