根据SELECT语句中的参数分配值 [英] Assigning a value based on a parameter in the SELECT statement
问题描述
我试图在字段中放置1或0,具体取决于参数的值。我需要使用此值来过滤为用户返回的数据。如果参数= 1,那么我想查看子选择中是否存在字段值,如果确实存在,则在PA字段中放置1,但如果不存在,则放置0.
我正在尝试使用CASE语句但是语法错误不正确。
我尝试过:
SELECT
CASE
WHEN @PAttendance = 1
THEN
Att.AdEnrollSchedID NOT IN
(
SELECT AdEnrollSchedID
来自AdAttend(NOLOCK)
WHERE AdAttend.Absent> 0
)= 0
ELSE
1
END PA
I am trying to place a 1 or 0 in a field depending on the value of a parameter. I am needing to use this value to filter the data that is returned for the user. If the parameter = 1 then I want to see if a field value exists in a subselect and if it does then place a 1 in the PA field but if it doesn't then place a 0.
I am trying to use a CASE statement but I am getting incorrect syntax errors.
What I have tried:
SELECT
CASE
WHEN @PAttendance = 1
THEN
Att.AdEnrollSchedID NOT IN
(
SELECT AdEnrollSchedID
FROM AdAttend(NOLOCK)
WHERE AdAttend.Absent > 0
) = 0
ELSE
1
END PA
推荐答案
使用IF ... ELSE并创建2SELECTS;而不是1 SELECT和一个CASE。
如果你无法理解一种方法,那就尝试另一种方法。在优化时,SQL Server也喜欢更简单的查询。
Use an "IF ... ELSE " and create "2" SELECTS; instead of 1 SELECT and a "CASE".
If you can't get your head around one method, try another. SQL Server also likes "simpler" queries when it comes to optimization.
这篇关于根据SELECT语句中的参数分配值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!