MySQL Case when then，但在 when 和 before then 内有 AND 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7294890/
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
Case when then, but with AND condition inside when and before then
提问by Anisoropos
In the below query I want to add an AND condition inside the CASE's WHEN and before THEN is that possible?
在下面的查询中，我想在 CASE 的 WHEN 和 THEN 之前添加一个 AND 条件，这可能吗？
for example WHEN 'r' AND table1.name="jones" THEN 'very high'
例如 WHEN 'r' AND table1.name="jones" THEN 'very high'
SELECT table1.id, table1.name,
CASE table1.event
WHEN 'r' THEN 'very high'
WHEN 't' THEN 'very low'
ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value
ORDER BY table2.value DESC LIMIT 1)
END AS risk
FROM table1
ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC
回答by Brian Glaz
You can rewrite your statement like this to accomplish what you want
你可以像这样重写你的语句来完成你想要的
SELECT table1.id, table1.name,
CASE
WHEN table1.event = 'r' AND table1.name = 'jones' THEN 'very high'
WHEN table1.event = 't' AND table1.name = 'smith' THEN 'very low'
ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value
ORDER BY table2.value DESC LIMIT 1)
END AS risk
FROM table1
ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC
notice that you need to remove table1.eventafter the CASEstatement.
documentation here
请注意，您需要table1.event在CASE语句之后删除。
文档在这里
回答by Josh Darnell
Anything that evaluates to a boolean (true or false) can go in the WHENcondition of a CASEstatement. So you can replace 'r'with:
任何评估为布尔值（真或假）的东西都可以放在语句的WHEN条件中CASE。所以你可以替换'r'为：
('r' AND table1.name='jones')
('r' AND table1.name='jones')
Thinking about this more, you might have to lose the table1.eventafter CASE
多考虑这个，你可能不得不失去table1.event之后CASE
SELECT table1.id, table1.name,
CASE
WHEN (table1.event = 'r' AND table1.name='Jones') THEN 'very high'
WHEN table1.event = 't' THEN 'very low'
ELSE (SELECT table2.risk
FROM table2
WHERE table2.value <= table1.value
ORDER BY table2.value DESC LIMIT 1)
END AS risk
FROM table1
ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC
回答by Andomar
Switch from case <column> when <value> then ...to case when <condition> then ...:
从 切换case <column> when <value> then ...到case when <condition> then ...：
CASE
WHEN table1.event = 'r' AND table1.active = 1 THEN 'very high'
...
