where子句中的用例 [英] Use case in where clause

问题描述

我正在尝试将case语句添加到查询中的where条件.我的查询如下所示-

I am trying to add case statement to where condition in a query. My query looks like below-

select * from table_name
where
if(id==1) then col1 = 0
else           col1 is null

如上面的查询所示，如果id-== 0，我想在where条件中添加col1 = 0，否则我希望col1在where条件中为null.我尝试过

As shown in above query I want to add col1 = 0 in where condition if id-==0 else I want col1 is null in where condition. I tried

select * from table_name
where 
case id =1 then (col1 = 0)
else col1 is null
end

但是语法不正确.有什么办法吗?

But the syntax is incorrect. Is there any way to do this?

推荐答案

SELECT *
FROM table_name
WHERE isnull(id, '')= CASE(id)
               WHEN 1
               THEN 0
               ELSE ''
           END;

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