Sql server查询汇总补缺月份
Excel做图表的时候，如果你坐标是日期的时候，它会自动给你在图标上给你补全日期，这个有人喜欢有人忧，不过我忧的不是在Excel中，我忧的是要在Appsmith中的图表中显示连续的月份，希望在sql server查询中要补全缺失的月份。
一.需求：通过销售表汇总出来的数据为：
月份 次数
1/2025 100
2/2025 300
4/2025 200
6/2025 50
在图表上我们希望显示的柱状图中这些缺失的月份需要用0补全，显示的数据为本月之前的连续12个月。
二.解法一：先汇总残缺数据再补全
先Sql Query汇总获取数据，然后JS再遍历补齐空缺，觉得太麻烦，放弃。
三.解法一：自定义月份表
定义一个序列表 MonthTable（01，02，03，04, 05,06，07，08，09，10，11，12），字段名为TMONTH，然后通过not in 补齐，因为会跨年，所以高于本月，年份就是取去年。
DECLARE
-- 结束日期（默认当前日期）
@EndDate DATE = GETDATE(),
--当前月份
@CurrentM TINYINT =MONTH(GETDATE()),
--去年
@LastY INT = YEAR(GETDATE())-1
-- 计算开始日期（结束日期往前推11个月）
DECLARE @StartDate DATE = DATEADD(MONTH, -11, @EndDate);
--合并
SELECT T.YearMonthSort,T.TotalCount FROM
(
--先汇总
( SELECT
FORMAT(SaleDate, 'MM/yyyy') AS YearMonthSort,
COUNT(1) AS TotalCount
FROM Sales
WHERE SaleDate BETWEEN @StartDate AND @EndDate
GROUP BY FORMAT(SaleDate, 'MM/yyyy')
)
UNION ALL
--补缺月份数据
(
SELECT CASE WHEN TMONTH>@CurrentM THEN CAST(TMONTH AS VARCHAR)+'/'+CAST(@LastY AS VARCHAR) ELSE CAST(TMONTH AS VARCHAR)+'/'+CAST(YEAR(GETDATE()) AS VARCHAR) END AS YearMonthSort ,
0 AS TotalCount
FROM MonthTable
WHERE TMONTH NOT IN (SELECT DISTINCT MONTH(SaleDate) FROM Sales)
)
) T ORDER BY T.YearMonthSort
四.解法三：公用表表达式（CTE）
WITH cte_name (column_name1, column_name2, ...)
AS
(
CTE_query_definition
)
SELECT * FROM cte_name;
其实原理和自定义类似，定义的是一个临时命名的结果集，供后续查询使用，不需要一个自定义的实体表。
DECLARE
@EndDate DATE = GETDATE(), -- 结束日期（默认当前日期）
@MonthCount Tinyint = 12 ; --12个月
-- 计算开始日期（结束日期往前推11个月）
DECLARE @StartDate DATE = DATEADD(MONTH, -11, @EndDate);
-- 生成连续月份序列的CTE
;WITH MonthSeries AS (
SELECT TOP (@MonthCount)
DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY object_id) - 1, @StartDate) AS MonthStart
FROM sys.all_objects
),
YearMonthSeries AS (
SELECT
FORMAT(MonthStart, 'yyyyMM') AS YearMonthSort,
FORMAT(MonthStart, 'MM/yyyy') AS YearMonthDisplay
FROM MonthSeries
),
-- 业务数据汇总
BusinessData AS (
SELECT
FORMAT(SaleDate, 'yyyyMM') AS YearMonthSort,
count(1) AS TotalCount
FROM Sales
WHERE SaleDate BETWEEN @StartDate AND @EndDate
GROUP BY FORMAT(SaleDate, 'yyyyMM')
)
-- 最终结果（确保连续12个月）
SELECT
y.YearMonthDisplay AS [年月],
ISNULL(b.TotalCount, 0) AS [销售次数]
FROM YearMonthSeries y
LEFT JOIN BusinessData b ON y.YearMonthSort = b.YearMonthSort
ORDER BY y.YearMonthSort;
其实自己喜欢第二个方法，直接简单，不过方法三看起来专业，练练CTE才用方法三，。