如何对多个表中的多个计数求和 [英] How to sum a multiple count from multiple tables

问题描述

希望有人可以帮助我。

对于我的忠诚计划，我会计算添加的歌曲数量，添加，对课程和歌曲的评论数量等。

For my Loyalty Program I count the amount of songs being added, amount of lessons being addes, amount of comments on lesson and songs and more.

对于名人堂，我想查看具有最高声望的会员。

For a hall of fame I would like to make a view of members with the highest reputation. and also add the reputation underneat the avatar.

因此我想总结一下：
TOTAL = totalsongs + totallesson + totalsongcomments + totallessoncomments

Therefor I would like to sum: TOTAL = totalsongs + totallesson + totalsongcomments + totallessoncomments

我的表格如下：

海报

poster_id| username | 
---------|----------|
1        | lisa
2        | john
3        | ben

课程

lesson_id| title    | poster_id
---------|----------|----------
1        | lesson1  | 1
2        | lesson2  | 1
3        | lesson3  | 2
4        | lesson4  | 3
5        | lesson5  | 1
6        | lesson7  | 2

Song

song_id  | title    | poster_id
---------|----------|----------
1        | song 1   | 1
2        | song 2   | 1
3        | song 3   | 2

SongComment

SongComment

com_id   | song_id  | poster_id | comment
---------|----------|-----------|--------
1        | 1        | 1         | This comment1
2        | 2        | 1         | This comment2
3        | 3        | 2         | This comment3

LessonComment

LessonComment

com_id   | lesson_id| poster_id | comment
---------|----------|-----------|--------
1        | 1        | 1         | This comment1
2        | 2        | 1         | This comment2
3        | 3        | 2         | This comment3

请帮助设置mysql查询

Please help to set up the mysql query

    SELECT poster.gebruikersnaam 
    SUM(
        (SELECT COUNT(*) FROM song) AS totalsongs +
        (SELECT COUNT(*) FROM lesson AS totallesson + 
        (SELECT COUNT(*) FROM songcomment AS totalsongcomments +
        (SELECT COUNT(*) FROM lessoncomment AS totallessoncomments +
        )
    FROM song
    INNER JOIN poster ON poster.poster_id = song.song_poster_id
    WHERE song.song_poster_id !=  '0'
    GROUP BY poster.poster_id
    ORDER BY TOTAL
    LIMIT 0 , 250

这是OKE但是仍然需要订购总额：）

THIS IS OKE BUT.. still has to order by the sum :)

SELECT P.poster_id,
  (SELECT COUNT(*) FROM song WHERE P.poster_id = song.song_poster_id) AS SongCount,
  (SELECT COUNT(*) FROM lesson WHERE P.poster_id = lesson.lesson_poster_id) AS LessonCount,
  (SELECT COUNT(*) FROM commentaar WHERE P.poster_id = commentaar.poster_id) AS SongCommCount,
  (SELECT COUNT(*) FROM lesson_comment WHERE P.poster_id = lesson_comment.lesson_comment_poster_id) AS LessonCommCount
FROM poster AS P
LIMIT 0, 50

EDIT 2

    SELECT PM.poster_id , PM.SongCount , PM.LessonCount, PM.SongCommCount, PM.LessonCommCount, (PM.SongCount + PM.LessonCount + PM.SongCommCount + PM.LessonCommCount) AS TotalCount 
FROM (
  SELECT P.poster_id, 
    (SELECT COUNT(*) FROM song WHERE P.poster_id = song.song_poster_id) AS SongCount, 
    (SELECT COUNT(*) FROM lesson WHERE P.poster_id = lesson.lesson_poster_id) AS LessonCount, 
    (SELECT COUNT(*) FROM commentaar WHERE P.poster_id = commentaar.poster_id) AS SongCommCount, 
    (SELECT COUNT(*) FROM lesson_comment WHERE P.poster_id = lesson_comment.lesson_comment_poster_id) AS LessonCommCount 
  FROM poster AS P 
  LIMIT 0, 50
) AS PM
ORDER BY (PM.SongCount + PM.LessonCount + PM.SongCommCount + PM.LessonCommCount) DESC

EDIT 3

    SELECT poster_id, 
       songCount, lessonCount, songCommentCount, lessonCommentCount,
       songCount + lessonCount + songCommentCount + lessonCommentCount as totalRank
FROM(SELECT poster.poster_id, 
            COALESCE(song.count, 0) as songCount,
            COALESCE(lesson.count, 0) as lessonCount,
            COALESCE(commentaar.count, 0) as songCommentCount,
            COALESCE(lesson_comment.count, 0) as lessonCommentCount
     FROM poster
     LEFT JOIN (SELECT song_poster_id, COUNT(*) as count
                FROM song
                GROUP BY song_poster_id) song
            ON song.song_poster_id = poster.poster_id  
     LEFT JOIN (SELECT lesson_poster_id, COUNT(*) as count
                FROM lesson
                GROUP BY lesson_poster_id) lesson
            ON lesson.lesson_poster_id = poster.poster_id
     LEFT JOIN (SELECT poster_id, COUNT(*) as count
                FROM commentaar
                GROUP BY poster_id) commentaar
            ON commentaar.poster_id = poster.poster_id
     LEFT JOIN (SELECT lesson_comment_poster_id, COUNT(*) as count
                FROM lesson_comment
                GROUP BY lesson_comment_poster_id) lesson_comment
            ON lesson_comment.lesson_comment_poster_id = poster.poster_id) Total
ORDER BY totalRank DESC
LIMIT 0, 50

推荐答案

除了@ Linger的回答，以下是其他常用的方法：

Other than @Linger's answer, here's the other usual way to do this:

SELECT poster_id, 
       songCount, lessonCount, songCommentCount, lessonCommentCount,
       songCount + lessonCount + songCommentCount + lessonCommentCount as totalRank
FROM(SELECT Poster.poster_id, 
            COALESCE(Song.count, 0) as songCount,
            COALESCE(Lesson.count, 0) as lessonCount,
            COALESCE(SongComment.count, 0) as songCommentCount,
            COALESCE(LessonComment.count, 0) as lessonCommentCount
     FROM Poster
     LEFT JOIN (SELECT poster_id, COUNT(*) as count
                FROM Song
                GROUP BY poster_id) Song
            ON Song.poster_id = Poster.poster_id  
     LEFT JOIN (SELECT poster_id, COUNT(*) as count
                FROM Lesson
                GROUP BY poster_id) Lesson
            ON Lesson.poster_id = Poster.poster_id
     LEFT JOIN (SELECT poster_id, COUNT(*) as count
                FROM SongComment
                GROUP BY poster_id) SongComment
            ON SongComment.poster_id = Poster.poster_id
     LEFT JOIN (SELECT poster_id, COUNT(*) as count
                FROM LessonComment
                GROUP BY poster_id) LessonComment
            ON LessonComment.poster_id = Poster.poster_id) Total
ORDER BY totalRank DESC
LIMIT 0, 50

（有一个工作 SQL Fiddle示例 - 感谢@Linger为设置）

我不知道足够的mySQL，知道这将是对你的情况更有表现。注意， LIMIT 子句不应该是一个问题，因为你希望它们排序。此外，如果您在每个子表中没有 poster_id 的索引，您可能需要它们...

(have a working SQL Fiddle example - thanks to @Linger for the setup)
I don't know enough about mySQL to know if this will be more performant for your situation. Note that the LIMIT clause shouldn't be a problem, given that you want them ordered anyways. Also, if you don't have indices over poster_id in each of the 'child' tables, you're probably going to want them...

这篇关于如何对多个表中的多个计数求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！