SQL SQLite 中的 IF() 语句替代
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4874285/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IF() statement alternative in SQLite
提问by VeroLom
I have the code for MySQL (perl):
我有 MySQL (perl) 的代码：
UPDATE pages
SET rkey = rkey + 2,
lkey = IF(lkey >= $key, lkey + 2, lkey)
WHERE rkey >= $key
I need to use this code with SQLite, but there is no support of IF() function. What I can do?
我需要将此代码与 SQLite 一起使用，但不支持 IF() 函数。我可以做什么？
回答by osgx
For generic SQL you can use CASE:
对于通用 SQL，您可以使用CASE：
CASE is used to provide if-then-else type of logic to SQL. Its syntax is:
SELECT CASE ("column_name") WHEN "condition1" THEN "result1" WHEN "condition2" THEN "result2" ... [ELSE "resultN"] END FROM "table_name"
CASE 用于为 SQL 提供 if-then-else 类型的逻辑。它的语法是：
SELECT CASE ("column_name") WHEN "condition1" THEN "result1" WHEN "condition2" THEN "result2" ... [ELSE "resultN"] END FROM "table_name"
From http://www.sqlite.org/lang_expr.htmlsection "The CASE expression"
来自http://www.sqlite.org/lang_expr.html部分“CASE 表达式”
E.g.
例如
UPDATE pages
SET rkey = rkey + 2,
lkey = CASE WHEN lkey >= $key THEN lkey + 2 ELSE lkey END
WHERE rkey >= $key
Another link about SQLite & CASE (with example of update with subselect) http://sqlite.awardspace.info/syntax/sqlitepg09.htm
另一个关于 SQLite & CASE 的链接（以子选择更新为例）http://sqlite.awardspace.info/syntax/sqlitepg09.htm
CASE can be used in UPDATE in generic SQL, but I have no info about SQLite support of UPDATEs with CASE
CASE 可以在通用 SQL 中的 UPDATE 中使用，但我没有关于 SQLite 对 CASE UPDATE 支持的信息
http://www.craigsmullins.com/ssu_0899.htmsection "Using CASE Expressions When Modifying Data"
http://www.craigsmullins.com/ssu_0899.htm部分“修改数据时使用 CASE 表达式”
回答by osgx
UPDATE pages
SET rkey = rkey + 2,
lkey = IF(lkey >= $key, lkey + 2, lkey)
WHERE rkey >= $key
??? it to
？？？它到
UPDATE pages
SET lkey = lkey + 2
WHERE rkey >= $key AND lkey >= $key
UPDATE pages
SET rkey = rkey + 2,
WHERE rkey >= $key
Isn't it better?
不是更好吗？
回答by Lukasz Szozda
SQLite version 3.32.0 and newer support IIF.
SQLite 版本 3.32.0 和更新的支持IIF。
The iif(X,Y,Z) function returns the value Y if X is true, and Z otherwise.
The iff(X,Y,Z) function is logically equivalent to and generates the same bytecode as the CASE expression "CASE WHEN X THEN Y ELSE Z END".
如果 X 为真，iif(X,Y,Z) 函数返回值 Y，否则返回 Z。
iff(X,Y,Z) 函数在逻辑上等同于并生成与 CASE 表达式“CASE WHEN X THEN Y ELSE Z END”相同的字节码。
E.g.
例如
UPDATE pages
SET rkey = rkey + 2,
lkey = IIF(lkey >= $key, lkey + 2, lkey)
WHERE rkey >= $key;
