java - mysql 两表 count 没有数据则返回0？

Question

问题列表：

用户回答问题情况列表：

现在的 问题是，我在select的时候 想直接显示：

username---------回答正确个数
st1------------------------- 0
st387944558-------------1

怎么才能达到以上效果呢？试了好多次都不行

Answer 1

You can post your SQL statement and see what errors there are

Answer 2

select username,count(*) 回答正确个数 from 回答问题情况列表 a,问题列表 b where a.question=b.id and a.answer=b.answer group by a.username;

Answer 3

SELECT
    username,
    count(t.answer)
FROM
    user_answer a
LEFT JOIN question t ON (
    a.question = t.question
    AND a.answer = t.answer
)
GROUP BY
    a.username

Answer 4

q table:

l table:

Result:

Solution:

SELECT
    l.username,
    IFNULL(c.count, 0) count
FROM
    l
LEFT JOIN (
    SELECT
        username,
        count(*) count
    FROM
        l,
        q
    WHERE
        l.question = q.id
    AND l.answer = q.answer
) c ON l.username = c.username
GROUP BY
    l.username