MySQL - 在 MySQL UPDATE 或 SELECT 查询中使用 If Then Else
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2177543/
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
MySQL - Using If Then Else in MySQL UPDATE or SELECT Queries
提问by ThinkCode
How do I update a table and set different values upon the condition evaluating to True.
如何在条件评估为 True 时更新表并设置不同的值。
For instance :
例如 ：
UPDATE Table
SET A = '1' IF A > 0 AND A < 1
SET A = '2' IF A > 1 AND A < 2
WHERE A IS NOT NULL;
I have seen CASE expression and IF expression in Procedures and Functions but I want to use it in a simple update/select statement. Is it possible or am I expecting too much from this lovely open source database?
我在过程和函数中看到了 CASE 表达式和 IF 表达式，但我想在一个简单的更新/选择语句中使用它。这个可爱的开源数据库有可能吗，还是我对它期望过高？
回答by Morfildur
UPDATE table
SET A = IF(A > 0 AND A < 1, 1, IF(A > 1 AND A < 2, 2, A))
WHERE A IS NOT NULL;
you might want to use CEIL()if Ais always a floating point value > 0and <= 2
您可能想要使用CEIL()ifA始终是一个浮点值> 0并且<= 2
回答by eggyal
Whilst you certainly canuse MySQL's IF()control flow function as demonstrated by dbemerlin's answer, I suspect it might be a little clearer to the reader (i.e. yourself, and any future developers who might pick up your code in the future) to use a CASEexpression instead:
虽然您当然可以使用dbemerlin 的回答所演示的MySQL 的IF()控制流函数，但我怀疑读者（即您自己，以及将来可能会拿起您的代码的任何未来开发人员）使用表达式可能会更清楚一些：CASE
UPDATE Table
SET A = CASE
WHEN A > 0 AND A < 1 THEN 1
WHEN A > 1 AND A < 2 THEN 2
ELSE A
END
WHERE A IS NOT NULL
Of course, in this specific example it's a little wasteful to set Ato itself in the ELSEclause—better entirely to filter such conditions from the UPDATE, via the WHEREclause:
当然，在这个特定的例子A中，在ELSE子句中设置为自己有点浪费——最好UPDATE通过WHERE子句从, 中完全过滤这样的条件：
UPDATE Table
SET A = CASE
WHEN A > 0 AND A < 1 THEN 1
WHEN A > 1 AND A < 2 THEN 2
END
WHERE (A > 0 AND A < 1) OR (A > 1 AND A < 2)
(The inequalities entail A IS NOT NULL).
（不等式包含A IS NOT NULL）。
Or, if you want the intervals to be closed rather than open (note that this would set values of 0to 1—if that is undesirable, one could explicitly filter such cases in the WHEREclause, or else add a higher precedence WHENcondition):
或者，如果您希望间隔关闭而不是打开（请注意，这会将值设置0为1- 如果这是不可取的，可以在WHERE子句中明确过滤此类情况，或者添加更高的优先级WHEN条件）：
UPDATE Table
SET A = CASE
WHEN A BETWEEN 0 AND 1 THEN 1
WHEN A BETWEEN 1 AND 2 THEN 2
END
WHERE A BETWEEN 0 AND 2
Though, as dbmerlin also pointed out, for this specific situation you could consider using CEIL()instead:
不过，正如 dbmerlin 还指出的，对于这种特定情况，您可以考虑使用CEIL()：
UPDATE Table SET A = CEIL(A) WHERE A BETWEEN 0 AND 2
回答by John Veldboom
Here's a query to update a table based on a comparison of another table. If record is not found in tableB, it will update the "active" value to "n". If it's found, will set the value to NULL
这是根据另一个表的比较更新表的查询。如果在 tableB 中没有找到记录，它会将“active”值更新为“n”。如果找到，则将该值设置为 NULL
UPDATE tableA
LEFT JOIN tableB ON tableA.id = tableB.id
SET active = IF(tableB.id IS NULL, 'n', NULL)";
Hope this helps someone else.
希望这对其他人有帮助。
