如果没有结果，MySql选择默认值吗? [英] MySql selecting default value if there are no results?

问题描述

我有2个表格:成员和评论. 我选择所有成员，然后加入评论. 但是在评论中，我选择了一些要点，如果用户未发表评论，我就无法在列表中显示该用户?！

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

因此，如果用户从不评论，如何将SUM的默认值选择为0，或采用其他解决方案:

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

---

我会尽力解释... 因此，这里有2个表，成员和注释.我需要列出所有具有排名的用户. 评论会获得所有投票和最佳答案.

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.

成员表:

member_id - username - avatar

评论表

comment_id - author_id - vote_value - best (0 OR 1)

也尝试从COMMENTS中选择并加入成员，但同样是这样:(

Also tried to select from COMMENTS and join MEMBERS, but same thing again :(

推荐答案

如果您只希望用户及其排名，我不确定为什么要在SELECT列表中包含comment_id.您是否只希望他们在特定评论上的排名?现在，我将给出一个解决方案，假设您只想要具有排名的完整成员列表:

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

(这假设vote_value和best均为NOT NULL列，或者MySQL在计算SUM值时将不理会这些列-我相信是的，但我尚未对此进行测试)

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

这篇关于如果没有结果，MySql选择默认值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！