MySQL SELECT 查询中的 IF 和 CASE 语句，其中包含子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15270942/
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 and CASE statements in SELECT query with a subquery in them
提问by BentCoder
Queries below return NULL if menu_items.parentis not 0which is wrong.
如果menu_items.parent不是0，则下面的查询返回 NULL，这是错误的。
What I'm trying to do here is, if value of menu_items.parentrow is 0then return it as original value but if the value is not 0then return the name (varchar) of the corresponding row of what ever the value is.
我在这里尝试做的是，如果menu_items.parent行的值为0，则将其作为原始值返回，但如果该值不为0，则返回该值对应的行的名称（varchar）。
Any idea why I keep getting NULL?
知道为什么我一直得到 NULL 吗？
Note: All the other selected columns comes as normal so the query itself is fine.
注意：所有其他选定的列都正常，因此查询本身没问题。
Thanks
谢谢
EXAMPLE 1:
例 1：
SELECT
...
...
(
CASE
WHEN menu_items.parent = '0' THEN menu_items.parent
ELSE (SELECT name FROM menu_items WHERE id = menu_items.parent)
END
) AS ParentID
...
...
FROM menus
INNER JOIN menu_items ...
...
...
EXAMPLE 2:
示例 2：
SELECT
...
...
IF
(
menu_items.parent = '0',
menu_items.parent,
(SELECT name FROM menu_items WHERE id = menu_items.parent)
) ParentID
...
...
FROM menus
INNER JOIN menu_items ...
...
...
ACTUAL QUERY:
实际查询：
SELECT
menus.name AS MenuName,
menu_items.id AS MenuItemsId,
menu_items.name AS MenuItemsName
/*
Sub section should go here
*/
FROM users
INNER JOIN assigned_menus ON assigned_menus.fk_users_id = users.id
INNER JOIN menu_items ON menu_items.id = assigned_menus.fk_menu_items_id
INNER JOIN menus ON menus.id = menu_items.fk_menus_id
WHERE
users.id = ? AND
users.is_active = 'YES' AND
menu_items.is_active = 'YES' AND
menus.is_active = 'YES'
ORDER BY
MenuName,
MenuItemsName
menu_items TABLE
菜单项表
ID name parent fk_menu_items_id
1 Menus 0 1
2 Roles 0 1
3 Permissions 0 1
4 Users 0 1
5 Files 0 1
6 File Uploads 0 1
7 University 6 1
8 Details 6 1
9 Progress 6 1
10 Assg 6 1
11 Applications 0 2
12 New 11 2
13 Edit 11 2
14 Rejected 11 2
15 Approved 11 2
16 Exs 0 2
17 Assm 16 2
18 Stf 0 3
19 Std 0 3
20 Prg 19 3
21 Comm 0 4
22 Sms 21 4
23 Sms2 21 4
24 New2 0 4
25 Act 0 4
回答by Romil Kumar Jain
SELECT
...
...
(
CASE
WHEN menu_items.parent = '0' THEN menu_items.parent
ELSE (SELECT mi.name FROM menu_items mi WHERE mi.id = menu_items.parent)
END
) AS ParentID
...
...
FROM menus
INNER JOIN menu_items ...
...
...
