如何使此 SQL 查询工作(带有嵌套查询的计算列)?
我希望计算列在字段 asofdate
等于表中的最大值 asofdate
时为 true,否则为 false。我尝试了以下操作,但出现语法错误。这样做的正确方法是什么?
select
case asofdate
when select max(asofdate) from sometable then 1
else 0
end
from sometable
或者,是否可以有一个计算列
case asofdate
when END OF PREVIOUS MONTH then 1
else 0
end
I want to have a computed column that is true if the field asofdate
is equal to the maximum asofdate
in the table, otherwise false. I tried the following but I am getting a syntax error. What is the right way to do this?
select
case asofdate
when select max(asofdate) from sometable then 1
else 0
end
from sometable
Alternatively, is it possible to have a computed column along the lines of
case asofdate
when END OF PREVIOUS MONTH then 1
else 0
end
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
来自 MSDN 文章
因此,不能以这种方式创建计算列。
From the MSDN article
So no you can't make a computed column that way.
您不能在计算列中执行此操作,因为计算列只能根据同一记录中其他列的值进行计算。
您可以在视图中执行此操作:
不幸的是,您无法为此视图建立索引。
You cannot do it in an computed column, since the computed column can be only computed from the values of other columns in the same record.
You can do it in a view, instead:
Unfortunately, you cannot index this view.
在 MS Sql Server 中
根据评论进行编辑
要获取上个月的月底 -
In MS Sql Server
EDIT based on comments
To get the end of the previous month -
我不知道这是否可行,但您可以创建一个公式为“MAX(asofdate)”的计算列,在该列上创建索引,然后创建一个仅将计算列与 asofdate 进行比较的视图。也许不是最好的解决方案,但可能比仅仅执行子查询稍好一些。
I dont know if this would work, but you could create a computed column with formula as 'MAX(asofdate)', create an index on that column and then create a view that just compares the computed column with asofdate. Maybe not the best possible solution, but might be slightly better than just doing a subquery.
所以
asofdate
的最大值存储在一个名为sometable
的表中,对吧?在这种情况下,您不必对每条记录使用子查询来检查它。您可以将其存储在变量中,然后检查当前的 asofdate。干杯!
so the maximum value of
asofdate
is stored in a table calledsometable
right? In that case you dont have to check it using a subquery for every record. You can stored it in a variable and then check the current asofdate towards that.Cheers!