如何在PostgreSQL中获取当前年份的所有月份（附C#实现）

1. PostgreSQL 直接生成当前年份的所有月份 #

如果仅需要当前年份的1-12月（不依赖订单表数据），可以用 generate_series 直接生成：

SELECT generate_series(1, 12) AS month;

若需要关联年份信息，可结合 date_trunc 生成当前年份的每个月日期后提取月份：

SELECT EXTRACT(MONTH FROM generate_series(
    date_trunc('year', CURRENT_DATE)::DATE,
    date_trunc('year', CURRENT_DATE)::DATE + INTERVAL '11 months',
    INTERVAL '1 month'
))::INT AS month;

2. 结合订单表统计每月销量（含无销量月份） #

如果要同时统计当前年份每个月的销量，且确保12个月都显示（无销量月份显示0），可以用生成的月份列表左关联订单表统计数据：

WITH all_months AS (
    SELECT generate_series(1, 12) AS month
),
monthly_sales AS (
    SELECT 
        EXTRACT(MONTH FROM orderdate)::INT AS month,
        COUNT(albumid) AS sales_count
    FROM public.orderalbum
    WHERE EXTRACT(YEAR FROM orderdate) = EXTRACT(YEAR FROM CURRENT_DATE)
    GROUP BY EXTRACT(MONTH FROM orderdate)::INT
)
SELECT 
    am.month,
    COALESCE(ms.sales_count, 0) AS sales_count
FROM all_months am
LEFT JOIN monthly_sales ms ON am.month = ms.month
ORDER BY am.month;

3. 优化自定义函数 #

你之前的 getyearfromdate 存在参数名与字段名冲突的问题，可简化为更高效的SQL函数：

获取年份的函数 #

CREATE OR REPLACE FUNCTION getyearfromdate(p_date DATE)
RETURNS INT AS $$
    SELECT EXTRACT(YEAR FROM p_date)::INT;
$$ LANGUAGE sql IMMUTABLE;

获取月份的函数（替代你的 getmonthfromdate） #

CREATE OR REPLACE FUNCTION getmonthfromdate(p_date DATE)
RETURNS INT AS $$
    SELECT EXTRACT(MONTH FROM p_date)::INT;
$$ LANGUAGE sql IMMUTABLE;

4. C# 端接口调整 #

修改接口使用关联查询，获取包含所有月份的销量统计：

// 按月统计销量（含当前年份所有月份）
[HttpGet]
public ActionResult GetMonthSales()
{
    string query = @"
        WITH all_months AS (
            SELECT generate_series(1, 12) AS month
        ),
        monthly_sales AS (
            SELECT 
                getmonthfromdate(orderdate) AS month,
                COUNT(albumid) AS sales_count
            FROM public.orderalbum
            WHERE getyearfromdate(orderdate) = getyearfromdate(CURRENT_DATE)
            GROUP BY getmonthfromdate(orderdate)
        )
        SELECT 
            am.month,
            COALESCE(ms.sales_count, 0) AS sales_count
        FROM all_months am
        LEFT JOIN monthly_sales ms ON am.month = ms.month
        ORDER BY am.month;
    ";

    using (NpgsqlCommand cmd = new NpgsqlCommand(query, conn))
    {
        List<MonthlySale> sales = new List<MonthlySale>();
        using (NpgsqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                sales.Add(new MonthlySale
                {
                    Month = reader.GetInt32(0),
                    SalesCount = reader.GetInt32(1)
                });
            }
        }
        return Ok(sales);
    }
}

// 辅助实体类
public class MonthlySale
{
    public int Month { get; set; }
    public int SalesCount { get; set; }
}

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