WHERE子句中的SQLite if语句 [英] SQLite if statement in WHERE clause

问题描述

我知道我可以在SQLite查询中使用CASE语句，但是我不知道如何在WHERE子句中构建它.

I know I can use CASE statement in an SQLite query but I don't know how to built it in a WHERE clause.

实际上，我在很长的WHERE子句中有此内容(这只是问题所涉及的部分):

Actually I have this in a long WHERE clause (this is just the part concerned by the question):

AND (%d >= (wines.year + wines.maturity)) AND (%d < (wines.year + wines.apogee))

事实上我只想要那个:

AND (%d >= (wines.year + wines.maturity))

=>如果wines.apogee为NULL

或:

AND (%d >= (wines.year + wines.maturity)) AND (%d < (wines.year + wines.apogee))

=>如果wines.apogee不为空

在这种情况下如何使用CASE语句测试wines.apogee是否不为NULL并在这种情况下添加请求的第二部分?

How to use the CASE statement in this case to test if wines.apogee IS NOT NULL and add the second part of the request in this case ?

谢谢！

推荐答案

CASE可以计算任何值，甚至可以是比较返回的布尔值. 在SQLite中，1与"true"相同:

CASE can compute any value, even a boolean value as returned by a comparison. In SQLite, 1 is the same as "true":

...
AND %d >= (wines.year + wines.maturity)
AND CASE WHEN wines.apogee IS NOT NULL
         THEN %d < (wines.year + wines.apogee)
         ELSE 1
    END
...

可以使用 ifnull()函数完成同样的操作:

...
AND %d >= (wines.year + wines.maturity)
AND ifnull(%d < (wines.year + wines.apogee), 1)
...

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