MySQL SELECT CASE WHEN something THEN 返回空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4659109/
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 SELECT CASE WHEN something THEN returning null
提问by baturalpdincdari
Table who
桌子 who
wid--name-------father---mother
1----Daisy------David----Liza
2----Jenny------Joe------Judy
3----Meggy------Mike-----Manuela
4----Sarah------Joe------Judy
5----Chelsea----Bill-----Hillary
6----Cindy------David----Liza
7----Kelly------Joe------Judy
Table ages
桌子 ages
aid---whoid---age
1-----1--------0
2-----2--------0
3-----3-------14
4-----4-------30
5-----5-------22
6-----6-------17
7-----1-------18
I want that list as a result:
结果我想要那个列表：
id---name------age
1----Meggy-----14
2----Cindy-----17
3----Daisy-----18 (Selected data that bigger than 0)
4----Chelsea---22
5----Sarah-----30
6----Jenny-----30 (Her age is 0 on ages table and Sarah's age with same father and mother)
7----Kelly-----30 (No data on ages table and Sarah's age with same father and mother)
I tried that query:
我试过那个查询：
SELECT
*,
(CASE age
WHEN '0' THEN (
SELECT age
FROM ages a
LEFT JOIN who w
ON w.wid = a.whoid
WHERE
w.father = father
AND
w.mother = mother
ORDER BY a.age DESC LIMIT 1
)
ELSE age
END
) AS newage
FROM who
LEFT JOIN ages
ON wid = whoid
ORDER BY newage
What's wrong with that?
那有什么问题？
回答by Quassnoi
CASE … WHEN NULLwill never match anything, and CASE NULLwill always match the ELSEclause (which in your case returns age, i. e. NULL).
CASE … WHEN NULL永远不会匹配任何内容，并且CASE NULL将始终匹配ELSE子句（在您的情况下返回age，即NULL）。
Use this:
用这个：
CASE COALESCE(age, 0) WHEN 0 THEN … ELSE age END
Update:
更新：
You also need to alias your tables and use the aliases in the field descriptions:
您还需要为表添加别名并在字段描述中使用别名：
SELECT *,
CASE COALESCE(age, 0)
WHEN '0' THEN
(
SELECT MAX(age)
FROM who wi
JOIN ages ai
ON ai.whoid = wi.wid
WHERE wi.father = w.father
AND wi.mother = w.mother
)
ELSE
age
END AS newage
FROM who w
LEFT JOIN
ages a
ON a.whoid = w.wid
ORDER BY
newage
