如何将SQL子查询赋值给变量并在同一上下文中使用它
如何将SQL子查询赋值给变量并在同一上下文中使用它
提问于 2012-07-31 22:41:08
在此select中，我希望获取上个月和本月的值并显示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回答 1
Stack Overflow用户
回答已采纳
发布于 2012-08-01 17:21:50
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11742661
复制相关文章
相似问题
腾讯云开发者
