mysql - SQL 使用 COUNT() 和 LEFT JOIN 合并两个或三个表

Question

我确信以前有人问过这个问题，但我无法让我的代码工作:(

用户提出了多个问题。我需要显示用户进行某种用户搜索，然后显示每个用户提出了多少问题。

表 1(用户):ID，用户名，密码，头像,注册日期，is_banned(等等...)

表 2(问题):ID，提问者_id，问题，答案1，答案2，答案 3(等等...)

我需要将两者合并，然后显示用户名等普通用户信息，同时还计算 Questioner_id 行并显示用户提出了多少问题。

这是我到目前为止所得到的，但它只是输出一个结果并计算所有内容:

SELECT 
    users.id,
    users.username,
    users.avatar_location,
    users.datetime,
    users.last_action,
    users.last_action_description,
    users.is_banned,
    COUNT(questions.questioner_id)
FROM 
    `users` 
LEFT JOIN 
    `questions` 
ON 
    users.id = questions.questioner_id
ORDER BY
    datetime 
    ASC 

对于我完全的“菜鸟”行为，我提前表示歉意。我已经研究了一两个小时如何执行此操作，但无法修复它。

非常感谢

编辑:

非常感谢您的帮助!我的最后一个附加问题是，我现在又多了第三个表，这次我想对已回答的问题进行计数，因为我已经提出了问题。

表 3(已回答的问题):ID，问题 ID，用户身份，正确(等等...)

我尝试将其添加到我的查询中，但它没有显示已回答问题的数量，而是重复了所提出问题的结果。

这是我更新的查询(再次抱歉，我只是在努力解决这个问题)

SELECT 
    users.id,
    users.username,
    users.avatar_location,
    users.datetime,
    users.last_action,
    users.last_action_description,
    users.is_banned,
    COUNT(questions_answered.question_id),
    COUNT(questions.questioner_id)
FROM 
    `users` 
LEFT JOIN 
    `questions` 
ON 
users.id = questions.questioner_id
LEFT JOIN
    `questions_answered`
ON
    users.id = questions_answered.user_id
GROUP BY
    users.id,
    users.username,
    users.avatar_location,
    users.datetime,
    users.last_action,
    users.last_action_description,
    users.is_banned
ORDER BY
    datetime 
    ASC 

非常感谢您的帮助!

Answer 1

第一部分(两个表)

我在查询字符串中有我的评论:

SELECT 
    users.id,
    users.username,
    users.avatar_location,
    users.datetime,
    users.last_action,
    users.last_action_description,
    users.is_banned,
    COUNT(questions.id) as number_of_questions #count each question
FROM 
    `users` 
LEFT JOIN 
    `questions` 
ON 
    users.id = questions.questioner_id
GROUP BY
    users.id # you need to have unique user id in each row
ORDER BY
    questions.datetime # sort by question date right? 
    DESC 

更新(三个表):

您需要嵌套选择，不能同时对两列执行计数。我们将第一个选择作为users_questions_count，就像一个表一样，然后一切都与两个表相同。有缺陷的部分可能会出现名称含糊不清的情况。

SELECT 
    users_questions_count.id,
    users_questions_count.username,
    users_questions_count.avatar_location,
    users_questions_count.datetime,
    users_questions_count.last_action,
    users_questions_count.last_action_description,
    users_questions_count.is_banned,
    users_questions_count.number_of_questions,
    COUNT(questions_answered.id) as number_of_answers # make sure you are counting the correct field!
FROM 
    (SELECT 
        users.id as id,
        users.username as username,
        users.avatar_location as avatar_location,
        users.datetime as datetime,
        users.last_action as last_action,
        users.last_action_description as last_action_description,
        users.is_banned as is_banned,
        COUNT(questions.id) as number_of_questions #count each question
    FROM 
        `users` 
    LEFT JOIN 
        `questions` 
    ON 
        users.id = questions.questioner_id
    GROUP BY
        users.id 
    ) as users_questions_count
LEFT JOIN 
    `questions_answered` 
ON 
    users_questions_count.id = questions_answered.user_id
GROUP BY
    users_questions_count.id 

三个表(table1、table2、table3 连接到 table1.id、table2.item_id，table3.item_id):

SELECT 
    table1_table2_count.id,
    table1_table2_count.counter_1,
    COUNT(table3.id) as counter_2
FROM 
    (SELECT 
        table1.id as id,
        COUNT(table2.id) as counter_1
    FROM 
        `table1` 
    LEFT JOIN 
        `table2` 
    ON 
        table1.id = table2.item_id
    GROUP BY
        table1.id 
    ) as table1_table2_count
LEFT JOIN 
    `table3` 
ON 
    table1_table2_count.id = table3.item_id
GROUP BY
    table1_table2_count.id 

但最好找到一种解决方案来避免在实际海量数据中进行此类选择。如果您可以更新一张表并在其上放置 counter_1 和 counter_2 ，则写入过程会变慢，但读取(搜索)会更快。