如何将VistaDB年度课程统计查询适配为支持多年半动态统计？

现有VistaDB查询语句，用于统计不同国家用户2019年各月份的课程启动数量。需将其修改为半动态形式：当WHERE子句结束日期设为'2022-12-31'时，自动生成2019至2022年所有月份的统计列。

原查询代码：

Select c.CountryName As Country,
  Count (case When Month( ch.CourseStarted ) = 1 Then 1 End) As Jan19,
  Count (case when Month(ch.CourseStarted  ) = 2 Then 1 End) as Feb19,
  Count (case When Month(ch.CourseStarted  ) = 3 Then 1 End) as Mar19,
  Count (case When Month(ch.CourseStarted  ) = 4 Then 1 End) as Apr19,
  Count (case When Month(ch.CourseStarted  ) = 5 Then 1 End) as May19,
  Count (case When Month(ch.CourseStarted  ) = 6 Then 1 End) as Jun19,
  Count (case When Month(ch.CourseStarted  ) = 7 Then 1 End) as Jul19,
  Count (case When Month(ch.CourseStarted  ) = 8 Then 1 End) as Aug19,
  Count (case When Month(ch.CourseStarted  ) = 9 Then 1 End) as Sep19,
  Count (case When Month(ch.CourseStarted  ) = 10 Then 1 End) as Oct19,
  Count (case When Month(ch.CourseStarted  ) = 11 Then 1 End)as Nov19,
  Count (case When Month(ch.CourseStarted  ) = 12 Then 1 End) as Dec19
From Country As c
  Inner Join CourseHistory As ch On c.Oid = ch.Country
Where (ch.CourseStarted >= '2019-01-01' And
       ch.CourseStarted <= '2019-12-31')
Group By c.CountryName
Order by c.CountryName;

VistaDB不支持原生动态列生成，需通过动态拼接SQL语句实现需求。核心逻辑是根据起始、结束日期自动生成对应年月的统计列，再执行拼接后的完整查询。

1. 定义日期参数 #

设定查询的时间范围，这里起始日期为'2019-01-01'，结束日期为'2022-12-31'：

DECLARE @StartDate DATE = '2019-01-01';
DECLARE @EndDate DATE = '2022-12-31';

2. 生成月份统计列片段 #

循环遍历时间范围内的每个月份，拼接出对应的COUNT(CASE...)统计语句：

DECLARE @MonthColumns NVARCHAR(MAX) = '';
DECLARE @CurrentDate DATE = @StartDate;

WHILE @CurrentDate <= @EndDate
BEGIN
    SET @MonthColumns = @MonthColumns + 
        'COUNT(CASE WHEN YEAR(ch.CourseStarted) = ' + CAST(YEAR(@CurrentDate) AS NVARCHAR(4)) + 
        ' AND MONTH(ch.CourseStarted) = ' + CAST(MONTH(@CurrentDate) AS NVARCHAR(2)) + 
        ' THEN 1 END) AS ' + FORMAT(@CurrentDate, 'MMMyy') + ',' + CHAR(13);
    
    -- 切换到下一个月
    SET @CurrentDate = DATEADD(MONTH, 1, @CurrentDate);
END

-- 移除末尾多余的逗号和换行
SET @MonthColumns = LEFT(@MonthColumns, LEN(@MonthColumns) - 2);

3. 拼接并执行完整SQL #

将生成的列片段与基础查询拼接，执行最终SQL：

DECLARE @FullSQL NVARCHAR(MAX) = '
SELECT c.CountryName AS Country,
' + @MonthColumns + '
FROM Country AS c
INNER JOIN CourseHistory AS ch ON c.Oid = ch.Country
WHERE ch.CourseStarted >= ''' + CAST(@StartDate AS NVARCHAR(10)) + ''' 
  AND ch.CourseStarted <= ''' + CAST(@EndDate AS NVARCHAR(10)) + '''
GROUP BY c.CountryName
ORDER BY c.CountryName;';

EXEC sp_executesql @FullSQL;

注意事项 #

• 修改@EndDate参数（如改为'2023-12-31'），查询会自动生成对应年份的所有月份统计列，无需手动修改CASE语句。
• FORMAT函数需VistaDB 5及以上版本支持，若版本较低，可手动拼接月份缩写（如Jan）和年份后两位替代。

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