sql - 如何将 SQL 子查询分配给变量并在同一上下文中使用它

Question

在此选择中，我想获取上个月和当前月份的值并显示 6 列:OLDVALUE 本身 ~ OLDVALUE X 28,8% ~ OLDVALUE X 8% ~ CURVALUE 本身 ~ CURVALUE X 28,8% ~ CURVALUE X 8%

但是我必须使用相同的子查询在前一个月进行 3 次，如下所示。这样我的代码将非常非常大并且性能较低(我想要更多信息)。

有没有办法分配一个“临时”变量来保持这个子查询的结果在同一上下文中再次使用，像循环结构中的变量一样逐行改变它的值？

问候。

/* == PARAMETERS == */
DECLARE @ANO INT = 2011
DECLARE @MES INT = 7

/* ===================================== SELECT ===================================== */
SELECT FUNC.CHAPA, FUNC.CODSECAO,
        (SELECT VALPROVFER FROM PFHSTPROV (NOLOCK)
          WHERE CHAPA = FUNC.CHAPA
            AND ANO = CASE WHEN (PROV.MES-1 = 0) THEN PROV.ANO-1 ELSE PROV.ANO END
            AND MES = CASE WHEN (PROV.MES-1 = 0) THEN 12 ELSE PROV.MES-1 END
        ) AS PROVFERMESANT,
        (SELECT VALPROVFER FROM PFHSTPROV (NOLOCK)
          WHERE CHAPA = FUNC.CHAPA
            AND ANO = CASE WHEN (PROV.MES-1 = 0) THEN PROV.ANO-1 ELSE PROV.ANO END
            AND MES = CASE WHEN (PROV.MES-1 = 0) THEN 12 ELSE PROV.MES-1 END
        )*0.288 AS PROVINSSMESANT,
        (SELECT VALPROVFER FROM PFHSTPROV (NOLOCK)
          WHERE CHAPA = FUNC.CHAPA
            AND ANO = CASE WHEN (PROV.MES-1 = 0) THEN PROV.ANO-1 ELSE PROV.ANO END
            AND MES = CASE WHEN (PROV.MES-1 = 0) THEN 12 ELSE PROV.MES-1 END
        ) * 0.08 AS PROVFGTSMESANT,
        PROV.VALPROVFER AS PROVFERMES, PROV.VALPROVFER*0.288 AS PROVINSSMES,
        PROV.VALPROVFER*0.08 AS PROVFGTSMES,
 FROM PFUNC FUNC (NOLOCK)
 JOIN PFHSTPROV PROV (NOLOCK) ON FUNC.CHAPA = PROV.CHAPA 
    AND FUNC.CODCOLIGADA = PROV.CODCOLIGADA
WHERE FUNC.CODSITUACAO = 'A'
    AND ANO = @ANO
    AND MES = @MES

Answer 1

   SELECT FUNC.CHAPA, FUNC.CODSECAO,
            isnull(PFH.VALPROVFER,0) AS PROVFERMESANT,
            isnull(PFH.VALPROVFER,0)*0.288 AS PROVINSSMESANT,
            isnull(PFH.VALPROVFER,0) * 0.08 AS PROVFGTSMESANT,

            PROV.VALPROVFER AS PROVFERMES, PROV.VALPROVFER*0.288 AS PROVINSSMES,
            PROV.VALPROVFER*0.08 AS PROVFGTSMES,
     FROM PFUNC FUNC (NOLOCK)
     JOIN PFHSTPROV PROV (NOLOCK) ON FUNC.CHAPA = PROV.CHAPA 
        AND FUNC.CODCOLIGADA = PROV.CODCOLIGADA

     LEFT JOIN PFHSTPROV PFH (NOLOCK) on 
                 ( (FUNC.CHAPA = PFH.CHAPA) 
                            AND  PFH.ANO = CASE WHEN (PROV.MES-1 = 0) THEN PROV.ANO-1 ELSE PROV.ANO END
                            AND PFH.MES = CASE WHEN (PROV.MES-1 = 0) THEN 12 ELSE PROV.MES-1 END        
                 ) 



    WHERE FUNC.CODSITUACAO = 'A'
        AND ANO = @ANO
        AND MES = @MES