SQL Server存储过程：指定月份及上月数据查询与字段优化

1. 在VARCHAR类型月份字段下获取上月数据 #

要同时获取指定月份和上月数据，核心是将月份名称转换为数字，计算上月的年份和对应月份名称，再匹配数据表中的记录。

具体实现（修改存储过程） #

ALTER PROCEDURE SP_NIVELES(
    @NIVEL VARCHAR(15),
    @MES VARCHAR(12),
    @AÑO INT
)
AS
BEGIN
    -- 校验线路参数
    IF @NIVEL <> 'LINEA MC'
    BEGIN
        RAISERROR('Linea Incorrecta', 16, 1)
        RETURN
    END

    DECLARE @MesNum INT, @AnioMesAnterior INT, @MesAnterior VARCHAR(12)

    -- 将月份名称转换为数字（示例为西班牙语月份，根据实际语言调整）
    SET @MesNum = CASE @MES
        WHEN 'Enero' THEN 1
        WHEN 'Febrero' THEN 2
        WHEN 'Marzo' THEN 3
        WHEN 'Abril' THEN 4
        WHEN 'Mayo' THEN 5
        WHEN 'Junio' THEN 6
        WHEN 'Julio' THEN 7
        WHEN 'Agosto' THEN 8
        WHEN 'Septiembre' THEN 9
        WHEN 'Octubre' THEN 10
        WHEN 'Noviembre' THEN 11
        WHEN 'Diciembre' THEN 12
    END

    -- 校验输入的月份名称是否有效
    IF @MesNum IS NULL
    BEGIN
        RAISERROR('Nombre de mes incorrecto', 16, 1)
        RETURN
    END

    -- 计算上月的年份和月份名称
    IF @MesNum = 1
    BEGIN
        SET @AnioMesAnterior = @AÑO - 1
        SET @MesAnterior = 'Diciembre'
    END
    ELSE
    BEGIN
        SET @AnioMesAnterior = @AÑO
        SET @MesAnterior = CASE @MesNum - 1
            WHEN 1 THEN 'Enero'
            WHEN 2 THEN 'Febrero'
            WHEN 3 THEN 'Marzo'
            WHEN 4 THEN 'Abril'
            WHEN 5 THEN 'Mayo'
            WHEN 6 THEN 'Junio'
            WHEN 7 THEN 'Julio'
            WHEN 8 THEN 'Agosto'
            WHEN 9 THEN 'Septiembre'
            WHEN 10 THEN 'Octubre'
            WHEN 11 THEN 'Noviembre'
        END
    END

    -- 查询指定月和上月的数据
    SELECT 
        LINEAMC, 
        SUM(MONTODEBITO) AS DEBITO, 
        SUM(MONTOCREDITO) AS CREDITO, 
        SUM(MONTODEBITO) - SUM(MONTOCREDITO) AS TOTAL 
    FROM PRUEBAOPEX p 
    WHERE 
        ((MES = @MES AND AÑO = @AÑO) OR (MES = @MesAnterior AND AÑO = @AnioMesAnterior))
    GROUP BY LINEAMC
END

注意：如果你的月份名称是其他语言（如英语），需要调整CASE语句中的对应值。

2. 改用日期类型实现高效查询 #

完全可以通过日期类型优化查询效率，VARCHAR类型的月份字段无法利用索引优化，且转换逻辑繁琐，日期类型则能直接利用SQL Server的日期函数和索引提升性能。

具体方案 #

步骤1：新增日期类型字段

在PRUEBAOPEX表中新增一个DATE类型的字段，用于存储每条记录对应的月份起始日期（如2024-01-01代表2024年1月）：

ALTER TABLE PRUEBAOPEX ADD FechaMes DATE;

步骤2：批量更新日期字段数据

将原有的MES和AÑO字段转换为FechaMes的值（同样以西班牙语月份为例）：

UPDATE PRUEBAOPEX
SET FechaMes = CAST(
    CAST(AÑO AS VARCHAR(4)) + '-' + 
    CASE MES
        WHEN 'Enero' THEN '01'
        WHEN 'Febrero' THEN '02'
        WHEN 'Marzo' THEN '03'
        WHEN 'Abril' THEN '04'
        WHEN 'Mayo' THEN '05'
        WHEN 'Junio' THEN '06'
        WHEN 'Julio' THEN '07'
        WHEN 'Agosto' THEN '08'
        WHEN 'Septiembre' THEN '09'
        WHEN 'Octubre' THEN '10'
        WHEN 'Noviembre' THEN '11'
        WHEN 'Diciembre' THEN '12'
    END + '-01'
AS DATE);

步骤3：创建索引优化查询

为新增的FechaMes字段创建非聚集索引，大幅提升查询速度：

CREATE NONCLUSTERED INDEX IX_PRUEBAOPEX_FechaMes ON PRUEBAOPEX(FechaMes);

步骤4：修改存储过程使用日期字段查询

ALTER PROCEDURE SP_NIVELES(
    @NIVEL VARCHAR(15),
    @MES VARCHAR(12),
    @AÑO INT
)
AS
BEGIN
    IF @NIVEL <> 'LINEA MC'
    BEGIN
        RAISERROR('Linea Incorrecta', 16, 1)
        RETURN
    END

    DECLARE @MesNum INT, @FechaActual DATE, @FechaAnterior DATE

    -- 转换月份名称为数字
    SET @MesNum = CASE @MES
        WHEN 'Enero' THEN 1
        WHEN 'Febrero' THEN 2
        WHEN 'Marzo' THEN 3
        WHEN 'Abril' THEN 4
        WHEN 'Mayo' THEN 5
        WHEN 'Junio' THEN 6
        WHEN 'Julio' THEN 7
        WHEN 'Agosto' THEN 8
        WHEN 'Septiembre' THEN 9
        WHEN 'Octubre' THEN 10
        WHEN 'Noviembre' THEN 11
        WHEN 'Diciembre' THEN 12
    END

    IF @MesNum IS NULL
    BEGIN
        RAISERROR('Nombre de mes incorrecto', 16, 1)
        RETURN
    END

    -- 生成指定月份和上月的起始日期
    SET @FechaActual = DATEFROMPARTS(@AÑO, @MesNum, 1)
    SET @FechaAnterior = DATEADD(MONTH, -1, @FechaActual)

    -- 查询数据
    SELECT 
        LINEAMC, 
        SUM(MONTODEBITO) AS DEBITO, 
        SUM(MONTOCREDITO) AS CREDITO, 
        SUM(MONTODEBITO) - SUM(MONTOCREDITO) AS TOTAL 
    FROM PRUEBAOPEX p 
    WHERE FechaMes IN (@FechaActual, @FechaAnterior)
    GROUP BY LINEAMC
END

优势：日期类型的查询逻辑更简洁，索引能直接被SQL Server利用，避免了VARCHAR字段的全表扫描，查询效率显著提升。

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