I have one select:
我有一个选择:
select
b.[FiscalPeriod],
b.[Column2],b.[Current_Value],
(
select TOP 1 [Column3] from [table1] b2
where month(b.ReportingPeriod) = month(DATEADD(month,1,b2.ReportingPeriod))
AND YEAR(b.ReportingPeriod) = YEAR(DATEADD(month,1,b2.ReportingPeriod))
AND b.id = b2.id
) as PREV_VALUE
FROM [table1] b
Now I'm doing: (b.[Current_Value]-PREV_VALUE) as difference
现在我正在做:(b。[Current_Value] -PREV_VALUE)作为差异
but I got error:
但是我得到了错误:
Invalid column name 'PREV_VALUE'
列名称'PREV_VALUE'无效
I know that instead of PREV_VALUE
, once again I can put sub select. But how to avoid repeat the select?
我知道,而不是PREV_VALUE,我可以再次选择sub。但是如何避免重复选择呢?
2 个解决方案
#1
3
You cannot access a table alias where it is defined. In your case, the best solution is probably outer apply
:
您无法访问定义它的表别名。在您的情况下,最好的解决方案可能是外部适用:
select b.[FiscalPeriod], b.[Column2],b.[Current_Value], bb.PREV_VALUE
FROM [table1] b OUTER APPLY
(select TOP 1 [Column3] as PREV_VALUE
from [table1] b2
where month(b.ReportingPeriod) = month(DATEADD(month,1,b2.ReportingPeriod)) AND
YEAR(b.ReportingPeriod) = YEAR(DATEADD(month,1,b2.ReportingPeriod)) AND
b.id = b2.id
order by ???
) bb
Then you can access the value more than once in the SELECT
.
然后,您可以在SELECT中多次访问该值。
Note: When using TOP
you should be using ORDER BY
, so you should fill in the ???
.
注意:使用TOP时你应该使用ORDER BY,所以你应该填写???。
#2
1
You can turn your query into a derived table or CTE. Then you can treat the aliases like columns:
您可以将查询转换为派生表或CTE。然后你可以像列一样处理别名:
SELECT *, (Current_Value-PREV_VALUE) AS difference
FROM (
Your current query
) q
#1
3
You cannot access a table alias where it is defined. In your case, the best solution is probably outer apply
:
您无法访问定义它的表别名。在您的情况下,最好的解决方案可能是外部适用:
select b.[FiscalPeriod], b.[Column2],b.[Current_Value], bb.PREV_VALUE
FROM [table1] b OUTER APPLY
(select TOP 1 [Column3] as PREV_VALUE
from [table1] b2
where month(b.ReportingPeriod) = month(DATEADD(month,1,b2.ReportingPeriod)) AND
YEAR(b.ReportingPeriod) = YEAR(DATEADD(month,1,b2.ReportingPeriod)) AND
b.id = b2.id
order by ???
) bb
Then you can access the value more than once in the SELECT
.
然后,您可以在SELECT中多次访问该值。
Note: When using TOP
you should be using ORDER BY
, so you should fill in the ???
.
注意:使用TOP时你应该使用ORDER BY,所以你应该填写???。
#2
1
You can turn your query into a derived table or CTE. Then you can treat the aliases like columns:
您可以将查询转换为派生表或CTE。然后你可以像列一样处理别名:
SELECT *, (Current_Value-PREV_VALUE) AS difference
FROM (
Your current query
) q