Блог

Главная - Вопросы по программированию - SQL — выберите для каждого пользователя общее количество статей и общее количество комментариев к статьям

SQL — выберите для каждого пользователя общее количество статей и общее количество комментариев к статьям

#mysql #sql #database

#mysql #sql #База данных

Вопрос:

Я пытаюсь получить для каждого пользователя общее количество статей и для каждой статьи общее количество комментариев, что-то вроде этого:

 username | total_articles | total_comments
John Doe |      3         |    10

До сих пор это мой SQL, я использую MySQL: 

 SELECT u.id, u.username, COUNT(a.id) AS total_articles, COUNT(c.id) AS total_comments FROM users u
LEFT JOIN articles a ON u.id = a.user_id
LEFT JOIN comments c ON a.id = c.article_id
GROUP BY u.id;

Я попытался сгруппировать по u.id , a.id c.id одновременно, но это работает некорректно.

Спасибо.

Комментарии:

1. Это просто мое предположение: вам нужно COUNT(DISTINCT c.id) вместо просто COUNT

2. СГРУППИРУЙТЕ ПО u.id , u.имя пользователя;

3. Я думаю, что DISTINCT это только для разных записей, мои записи уже различны, потому что я использую первичные ключи.

4. Все еще не работает, результат выдает неправильные значения для total_articles и total_comments. Спасибо!

5. Можете ли вы поделиться некоторыми примерами данных и результатом, который вы получаете для них?

Ответ №1:

В первом запросе представлены все статьи пользователя, во втором — все комментарии, к которым присоединился пользователь

отредактировано: используйте ЛЕВОЕ СОЕДИНЕНИЕ вместо JOIN

 SELECT id_total_articles, username, total_articles, total_comments 
              FROM 
                  (
                    SELECT u.id as id_total_articles, u.username, COUNT(a.id) AS total_articles FROM users u
                    LEFT JOIN articles a ON u.id = a.user_id
                    GROUP BY u.id, u.username
                ) as  AC 
                 left join 
                (
                  SELECT u.id as id_total_comments, COUNT(c.id) AS total_comments FROM users u
                  LEFT JOIN comments c ON u.id = c.user_id
                  GROUP BY u.id, u.username 
                ) as  CC 
            ON AC.id_total_articles = CC.id_total_comments;

Комментарии:

1. установите флажок редактировать, используйте соединение по левому краю, потому что, если у пользователя нет комментариев и статей, он не будет отображаться

2. Я заметил эту вещь, но я был доволен ответом, не, все просто идеально, спасибо! Я узнаю больше о SQL, поэтому пробую разные вещи, спасибо!

Ответ №2:

используйте u.id, u.username оба столбца в group by

 SELECT u.id, u.username, COUNT(a.id) AS total_articles,
 COUNT(c.id) AS total_comments FROM users u
LEFT JOIN articles a ON u.id = a.user_id
LEFT JOIN comments c ON a.id = c.article_id
GROUP BY u.id, u.username

Ответ №3:

Если вам нужно количество статей для пользователя и общее количество комментариев ко всем статьям пользователя — тогда это ваш запрос:

 SELECT
    u.id,
    u.username,
    COUNT(DISTINCT a.id) AS total_articles,
    COUNT(c.id) AS total_comments
FROM users u
LEFT JOIN articles a 
    ON u.id = a.user_id
LEFT JOIN comments c 
    ON a.id = c.article_id
GROUP BY
    u.id,
    u.username;

Но — если вы ищете количество комментариев (просто мысль здесь) пользователя — тогда вы хотите присоединиться к таблице комментариев по идентификатору пользователя, а не по идентификатору статьи.

Комментарии:

1. Я хочу показать количество статей, написанных каждым пользователем, и для каждой статьи я хочу показать общее количество комментариев, сделанных к этой статье всеми. Я просто пытаюсь больше узнать о SQL, и у меня была эта идея в голове..

2. Я проверил результаты своего запроса в db-fiddle. И они верны. Возможно, вы можете попытаться объяснить еще раз, что именно вы хотите увидеть в таблице результатов запроса?

3. Кто-то дал мне правильный ответ, проверьте его выше, это был мой желаемый результат.

Ответ №4:

Вам не хватает u.username в group by , также COUNT(a.id) необходимо изменить на COUNT(distinct a.id) :

 SELECT u.id, u.username, COUNT(distinct a.id) AS total_articles, COUNT(c.id) AS total_comments FROM users u
LEFT JOIN articles a ON u.id = a.user_id
LEFT JOIN comments c ON a.id = c.article_id
GROUP BY u.id, u.username;

Обновить:

Однако, я предполагаю, что вам на самом деле нужно что-то другое, чем предложенный вами запрос. Вы сказали, что вам нужно общее количество статей для каждого пользователя и общее количество комментариев для каждой статьи. Это означает, что вам нужны два отдельных запроса: 

 SELECT a.id article_id , COUNT(c.id) AS total_comments
FROM articles a 
LEFT JOIN comments c ON a.id = c.article_id
GROUP BY a.id

SELECT u.id, u.username, COUNT(distinct a.id) AS total_articles
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
GROUP BY u.id, u.username;

Комментарии:

1. Комментарии есть только в одной статье, но результат говорит мне, что есть другая статья, в которой есть один комментарий (неверный). Спасибо!

2. В скрипте есть два комментария к статье номер 2 и один комментарий к статье номер 1.

3. Я обновил ответ, проверьте, это ли то, что вы ищете.

4. Кто-то дал правильный ответ, проверьте его выше, это то, чего я хотел достичь. Спасибо!

Ответ №5:

Вы агрегируете по связанным измерениям и получаете избыточный подсчет.

Один из подходов заключается в использовании нескольких агрегатов:

 SELECT u.id, u.username, COUNT(a.id) AS total_articles,
       SUM(c.num_comments) AS total_comments
FROM users u LEFT JOIN
     articles a
     ON a.user_id = a.id LEFT JOIN
     (SELECT c.article_id, COUNT(c.id) as num_comments
      FROM comments c
      GROUP BY c.article_id
     ) c
     ON a.id = c.article_id
GROUP BY u.id, u.username;

Комментарии:

1. Я попробовал вашу версию, она не работает, но спасибо, что дали больше идей.

2. @MeOnly . . . Кроме неправильного подсчета 0 значений (что теперь исправлено), есть ли какие-либо другие проблемы?

3. Ваш ответ дает мне 0, когда дело доходит до подсчета, проверьте ответ выше, кто-то понял это правильно, но спасибо!