如何替换SQL查询中生成月份的master..spt_values以适配Azure SQL

Azure SQL 不支持直接引用master库的spt_values系统表，你可以用以下两种方案快速替换： #

方案1：查询内直接构造月份序列（无需建表，最快可用） #

直接用VALUES子句生成1-12的固定月份序列，替换原有查询中spt_values相关的逻辑即可，修改后的完整查询如下：

SELECT 
    months.number as Month, 
    ISNULL(t2.NumVisits, 0) AS NumVisits, 
    ISNULL(t2.NumActivities, 0) AS NumActivities,
    ISNULL(t2.Estimated, 0) AS Estimated,
    ISNULL(t2.Accepted, 0) AS Accepted,
    ISNULL(t2.PercAccepted, 0) AS PercAccepted,
    ISNULL(t2.PercValue, 0) AS PercValue
-- 替换原来的master..spt_values引用，直接构造1-12月序列
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS months(number)
LEFT JOIN 
    (SELECT 
        *,
        CASE WHEN t1.NumVisits <> 0 THEN (CAST(t1.NumActivities AS DECIMAL) / t1.NumVisits) * 100 ELSE 0 END AS PercAccepted,
        CASE WHEN t1.Estimated <> 0 THEN (CAST(t1.Accepted AS DECIMAL) / t1.Estimated) * 100 ELSE 0 END AS PercValue
    FROM
        (SELECT
            MONTH(DateVisit) AS Month, 
            COUNT(*) AS NumVisits,
            SUM(CASE WHEN DateActivity is not null THEN 1 ELSE 0 END) AS NumActivities,
            SUM(Estimate) AS Estimated,
            SUM(CASE WHEN DateActivity is not null THEN Estimate ELSE 0 END) AS Accepted
        FROM [dbo].[Activities]
        WHERE 
            DateVisit IS NOT NULL 
            AND (@Year IS NULL OR YEAR(DateVisit) = @Year) 
            AND (@ClinicID IS NULL OR ClinicID = @ClinicID)
            AND (@MedicalID IS NULL OR MedicalID = @MedicalID)
            AND (@TreatmentTypeID IS NULL OR TreatmentTypeID = @TreatmentTypeID)
        GROUP BY MONTH(DateVisit)) t1
        ) t2
    ON months.number = t2.Month
WHERE 
    -- 删掉原来的Type = 'P' AND number BETWEEN 1 AND 12条件，已经不需要了
    (@QuarterID IS NULL 
        OR (@QuarterID = 1 AND months.number <= 3) 
        OR (@QuarterID = 2 AND months.number > 3 AND months.number <= 6) 
        OR (@QuarterID = 3 AND months.number > 6 AND months.number <= 9) 
        OR (@QuarterID = 4 AND months.number > 9 AND months.number <= 12))
ORDER BY months.number

方案2：创建本地永久月份维度表（适合多查询复用） #

如果你的业务系统里有多个查询都需要补全月份维度，可以创建一个本地的小表长期使用：

建表&初始化代码： #

-- 创建月份表
CREATE TABLE dbo.DimMonth (
    MonthNo TINYINT NOT NULL PRIMARY KEY,
    QuarterNo TINYINT NOT NULL -- 也可以提前把季度字段加上，减少查询时的判断逻辑
);
-- 插入1-12月数据
INSERT INTO dbo.DimMonth(MonthNo, QuarterNo)
VALUES 
(1,1),(2,1),(3,1),
(4,2),(5,2),(6,2),
(7,3),(8,3),(9,3),
(10,4),(11,4),(12,4);

替换后的查询逻辑： #

把原来的FROM master..spt_values months替换为FROM dbo.DimMonth months，字段对应调整即可，甚至可以直接用表内预存的季度字段简化WHERE条件的判断。 #

两种方案都可以完美兼容Azure SQL，单场景使用选方案1即可，修改成本极低；多查询复用选方案2可减少重复代码。

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