如果没有结果，MySql 选择默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2922637/
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 selecting default value if there are no results?
提问by Kenan
i'm having 2 tables: members and comments. I select all members, and then join comments. But in comments I'm selecting some SUM of points, and if user never commented, I can't get that user in listing?!
我有 2 个表：成员和评论。我选择所有成员，然后加入评论。但是在评论中我选择了一些积分，如果用户从未评论过，我不能让该用户出现在列表中？！
So how to select default value for SUM to be 0 if user never commented, or some other solution:
那么如果用户从未评论过，如何选择 SUM 的默认值为 0，或者其他一些解决方案：
SELECT c.comment_id AS item_id, m.member_id AS member_id, m.avatar,
SUM(c.vote_value) AS vote_value, SUM(c.best) AS best,
SUM(c.vote_value) + SUM(c.best)*10 AS total
FROM members m
LEFT JOIN comments c ON m.member_id = c.author_id
GROUP BY c.author_id
ORDER BY m.member_id DESC
LIMIT 0, 20
EDIT:
编辑：
I will try to explain... So there are 2 tables, members and comments. I need listing of all users with ranking. Comments hold all votes and best answers.
我将尝试解释...所以有 2 个表、成员和评论。我需要列出所有具有排名的用户。评论包含所有投票和最佳答案。
So, I need listing of all users, and they score.
所以，我需要列出所有用户，然后他们得分。
Members table:
成员表：
member_id - username - avatar
Comments table
评论表
comment_id - author_id - vote_value - best (0 OR 1)
Also tried to select from COMMENTS and join MEMBERS, but same thing again :(
还尝试从评论中选择并加入成员，但同样的事情:(
回答by Tom H
I'm not sure why you are including the comment_id in your SELECT list if you just want users and their rankings. Do you want only their ranking on that particular comment? I'll give a solution for now that assumes you just want a full member list with rankings:
如果您只想要用户及其排名，我不确定为什么要在 SELECT 列表中包含 comment_id。您是否只想要他们对该特定评论的排名？我现在给出一个解决方案，假设您只想要一个带有排名的完整成员列表：
SELECT
M.member_id,
M.user_id,
M.avatar,
COALESCE(SUM(C.vote_value), 0) AS vote_value_sum,
COALESCE(SUM(C.best), 0) AS best_sum,
COALESCE(SUM(C.vote_value), 0) + SUM(C.best) * 10 AS total_value
FROM
Members M
LEFT OUTER JOIN Comments C ON
C.author_id = M.member_id
GROUP BY
M.member_id
ORDER BY
total_value DESC
LIMIT 0, 20
(this assumes that vote_value and best are NOT NULL columns or that MySQL will disregard those when calculating SUM values - I believe that it does, but I haven't tested that)
（这假设 vote_value 和 best 是 NOT NULL 列，或者 MySQL 在计算 SUM 值时会忽略这些列 - 我相信它确实如此，但我还没有测试过）
回答by Michael Todd
MySQL has an IFNULL operator which allows you to return a value other than null if the result is null.
MySQL 有一个 IFNULL 运算符，它允许您在结果为 null 时返回 null 以外的值。
SELECT c.comment_id AS item_id, m.member_id AS member_id, m.avatar,
SUM(IFNULL(c.vote_value, 0)) AS vote_value, SUM(IFNULL(c.best, 0)) AS best,
SUM(IFNULL(c.vote_value, 0)) + SUM(IFNULL(c.best, 0))*10 AS total
FROM members m
LEFT JOIN comments c ON m.member_id = c.author_id
GROUP BY c.author_id
ORDER BY m.member_id DESC
LIMIT 0, 20
As others mentioned, COALESCE does something similar (and also works in MySQL).
正如其他人提到的，COALESCE 做了类似的事情（也适用于 MySQL）。
回答by David M
I use COALESCEfor this sort of thing.
我COALESCE用于这种事情。
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_coalesce
http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_coalesce
I would rewrite your second and third lines as:
我会把你的第二行和第三行改写为：
COALESCE(SUM(c.vote_value), 0) AS vote_value, COALESCE(SUM(c.best),0) AS best,
COALESCE(SUM(c.vote_value), 0) + COALESCE(SUM(c.best),0) * 10 AS total
回答by nathan
I don't think the SUM is the problem. I think it may be your
我不认为 SUM 是问题所在。我想这可能是你的
GROUP BY c.author_id
where you are grouping by the right side of a left outer join. I'm not sure what that does when the right side doesn't exist, but I'm guessing that's not what you really want. You most likely should be grouping by something in members, not comments.
您按左外连接的右侧分组的位置。我不确定当右侧不存在时会做什么，但我猜这不是你真正想要的。您很可能应该按成员中的某些内容分组，而不是评论。
Having said that, yes, you should also use COALESCE or IFNULL to turn null values to 0.
话虽如此，是的，您还应该使用 COALESCE 或 IFNULL 将空值变为 0。
回答by Toni Rosa
You could have a look to the CASE Statement, too: http://dev.mysql.com/doc/refman/5.0/en/case-statement.html
您也可以查看 CASE 声明：http: //dev.mysql.com/doc/refman/5.0/en/case-statement.html
In your case the result would be similar to...
在您的情况下，结果将类似于...
SELECT c.comment_id AS item_id, m.member_id AS member_id, m.avatar,
(CASE SUM(c.vote_value) WHEN NULL THEN 0
ELSE SUM(c.vote_value) END) AS vote_value, SUM(c.best) AS best,
(CASE SUM(c.vote_value) + SUM(c.najbolji)*10 WHEN null THEN 0
ELSE SUM(c.vote_value) + SUM(c.najbolji)*10 END) AS total
FROM members m
LEFT JOIN comments c ON m.member_id = c.author_id
GROUP BY c.author_id
ORDER BY m.member_id DESC
LIMIT 0, 20
回答by Jürgen Steinblock
You could use the if statement to convert NULL to 0
您可以使用 if 语句将 NULL 转换为 0
SELECT c.comment_id AS item_id, ...
IF(SUM(c.vote_value) is null, 0, SUM(c.vote_value)) as vote_value
FROM members m
LEFT JOIN comments c ON ...
