根据不同时间段创建SQL数据透视表

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
);

SELECT 
    YEAR(sale_date) AS sale_year,
    SUM(quantity * price) AS total_sales
FROM 
    sales
GROUP BY 
    YEAR(sale_date)
ORDER BY 
    sale_year;

SELECT 
    YEAR(sale_date) AS sale_year,
    MONTH(sale_date) AS sale_month,
    SUM(quantity * price) AS total_sales
FROM 
    sales
GROUP BY 
    YEAR(sale_date), MONTH(sale_date)
ORDER BY 
    sale_year, sale_month;

SELECT 
    sale_date,
    SUM(quantity * price) AS total_sales
FROM 
    sales
GROUP BY 
    sale_date
ORDER BY 
    sale_date;

SELECT 
    *
FROM (
    SELECT 
        YEAR(sale_date) AS sale_year,
        MONTH(sale_date) AS sale_month,
        SUM(quantity * price) AS total_sales
    FROM 
        sales
    GROUP BY 
        YEAR(sale_date), MONTH(sale_date)
) AS SourceTable
PIVOT (
    SUM(total_sales)
    FOR sale_month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS PivotTable
ORDER BY 
    sale_year;