mysql 结果默认值,如果没有结果，MySQL选择默认值吗？

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?!

So how to select default value for SUM to be 0 if user never commented, or some other solution:

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.

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 :(

解决方案

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

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)