sql查询where子句中的if条件 [英] if condition in where clause of sql query

问题描述

下面是我想要特定用户的数据时查询的 where 子句

Below is my where clause of the query when i want data for a specific user

where Completion_Date>= '11/01/2011' 
and Completion_Date<= '12/11/2012' 
and System_user_id = 1234

以下是我想为所有用户提取数据时的 where 子句:

and below is the where clause when i want to pull data for all the user:

where Completion_Date>= '11/01/2011' 
and Completion_Date<= '12/11/2012'

由于我不想要 2 个单独的查询，有没有办法在 where 子句中添加条件，以便我可以使用单个查询，并根据输入(即 System_user_id)决定是否在询问.当我想要所有用户的数据时，我将发送 -1对于特定用户，其 system_user_id 将被发送.

Since i dont want 2 seperate queries, is there a way to add a condition in where clause so that i can use a single query and depending on the input (i.e System_user_id) it will decide on whether to add extra condtion in the query. I will be sending -1 when i want data for all users & for a specific user its system_user_id will be sent.

推荐答案

您可以尝试以下步骤.

更新的查询

declare @userid int = -1
if (@userid = -1)
    BEGIN

     SELECT * FROM mytable 
     where Completion_Date>= '11/01/2011' 
     and Completion_Date<= '12/11/2012' 
     and userid in 
         (select distinct userID from mytable)
    end
ELSE
    BEGIN

     SELECT * FROM mytable 
     where Completion_Date>= '11/01/2011' 
     and Completion_Date<= '12/11/2012' 
     and userid = @userid 

end;

结果:

USERID  NAME    COMPLETION_DATE
123     john    2011-11-01
125     tim     2011-11-02
127     ron     2011-11-08

• 参考 SQLFIDDLE

查看特定用户:

• 参考SQLFIDDLE

根据 OP 的最新评论更新

Updated after latest comment from OP

• SQLFIDDLE

查询:

DECLARE @uid int = -1
SELECT 
*
FROM mytable 
WHERE 
( CASE 
     WHEN @uid <> -1 THEN @uid
     ELSE userid
  END
) = userid

and Completion_Date>= '11/01/2011' 
     and Completion_Date<= '12/11/2012' 
;

结果:当@uid = -1

Results: when @uid = -1

USERID  NAME    COMPLETION_DATE
123     john    2011-11-01
125     tim     2011-11-02
127     ron     2011-11-08

如果您尝试过，请发表评论:)

Please comment if you have tried this out :)

这篇关于sql查询where子句中的if条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！