如果使用“AND”,如何使用/“SQL中的OR运算符 [英] How to use if else using "AND" / "OR operator in SQL
问题描述
我不想使用if else用于非常小的目的,它让我一次又一次地在块中编写代码。
如何使用AND / OR运算符编写它:
if ( @ datafor year = 0)
选择 * 来自 abc
else
选择 * 来自 employee
其中 dataforyear = @ dataforyear
我想你提到了两个不同的这两个SELECT查询中的表名。我喜欢这样的东西。查看该文章以获取更多示例。
DECLARE @ dataforyear AS NUMERIC
DECLARE @ SQLQuery AS NVARCHAR ( 500 )
SET @dataforyear = 0
SET @SQLQuery = ' SELECT * FROM employee'
IF ( @ dataforyear <> 0 )
BEGIN
@ SQ LQuery = @ SQLQuery + ' WHERE dataforyear =' + @ dataforyear
END
EXECUTE sp_executesql @ SQLQuery
在存储过程中构建动态SQL [ ^ ]
这应该符合你的目的。
选择 *
来自员工
其中(@ dataforyear = 0 OR dataforyear = @ dataforyear)
您的要求非常简单,当然,如果您知道SQL中的NULLIF,COALESCE函数,它也不需要使用AND / OR。您的查询可以修改如下。
SELECT *
FROM 员工
WHERE dataforyear = COALESCE ( NULLIF ( @ dataforyear , 0 ),dataforyear)
说明:
如果@dataforyear = 0,则NULLIF(@ dataforyear,0)将变为NULL。然后COALESCE(NULLIF(@dataforyear,0),dataforyear)将为COALESCE(NULL,dataforyear)为您提供数据作为回报。
如果@dataforyear<> 0然后NULLIF(@dataforyear,0)将成为@dataforyear。然后COALESCE(NULLIF(@dataforyear,0),dataforyear)将COALESCE(@dataforyear,dataforyear)给你@dataforyear作为回报。
享受quries的乐趣。
I dont want to use if else for very small purpose which took me to write code again and again in the blocks.
How to write it using AND / OR operator:
if (@datafor year=0)
select * from abc
else
select * from employee
where dataforyear=@dataforyear
I think you have mentioned two different table names in those two SELECT query. I prefer something like this. Check that article for few more examples.
DECLARE @dataforyear AS NUMERIC DECLARE @SQLQuery AS NVARCHAR(500) SET @dataforyear = 0 SET @SQLQuery = 'SELECT * FROM employee ' IF (@dataforyear <> 0) BEGIN @SQLQuery = @SQLQuery + ' WHERE dataforyear = ' + @dataforyear END EXECUTE sp_executesql @SQLQuery
This should serve your purpose.
select * from employee where (@dataforyear=0 OR dataforyear=@dataforyear)
Your requirement is very simple and of course it has no need to use AND/OR also, if you know NULLIF, COALESCE functions in SQL. Your query can be modified as follows.
SELECT * FROM Employee WHERE dataforyear = COALESCE(NULLIF(@dataforyear, 0), dataforyear)
Explanation:
If @dataforyear = 0 then NULLIF(@dataforyear, 0) will become NULL. Then COALESCE(NULLIF(@dataforyear, 0), dataforyear) will be COALESCE(NULL, dataforyear) gives you dataforyear in return.
If @dataforyear <> 0 then NULLIF(@dataforyear, 0) will become @dataforyear. Then COALESCE(NULLIF(@dataforyear, 0), dataforyear) will be COALESCE(@dataforyear, dataforyear) gives you @dataforyear in return.
Have fun with quries.
这篇关于如果使用“AND”,如何使用/“SQL中的OR运算符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!