MYSQL将所有分组结果与行计数一次查询

Question

So I have searched everywhere for this but I can't find this specific relation. The query below returns all posts from a user and the number of people who liked the post because I used a GROUP BY postid meaning that if the result repeat themselves but have the same postid then they are grouped.

---

SELECT posts.id postid,posts.post_body,posts.post_type, ALLUSERS.USERNAME,
       likes.liker,likes.target,
       plikers.*,
       COUNT(posts.id) numberOflikes

FROM posts
INNER JOIN ALLUSERS ON(ALLUSERS.USERID=posts.FROM_userid)
LEFT JOIN likes ON(likes.target=posts.id)
LEFT JOIN(SELECT USERID pl_id FROM ALLUSERS )plikers ON(pl_id=likes.liker)
GROUP BY postid

And the result is...

+--------+-----------------+------------------------+-----------+-------+--------+-------+-----------+---------------+
| postid | post_body       | post_type              | USERNAME  | liker | target | pl_id | pl_un     | numberOflikes |
+--------+-----------------+------------------------+-----------+-------+--------+-------+-----------+---------------+
|     83 | Southgate       | 20&&03 Saturday/04:05  | Superuser |  NULL | NULL   |  NULL | NULL      |             1 |
|     84 | Great post!     | 10&&03 Saturday/04:07  | Superuser |     4 | 84     |     4 | dennisrec |             7 |
|     85 | How delightful? | 10&&03 Saturday/04:07  | Superuser |    43 | 85     |    43 | zerCon    |             1 |
|     87 | Cheers...       | 10&&07 Wednesday/01:53 | Superuser |  NULL | NULL   |  NULL | NULL      |             1 |
|     88 | check this out! | 20&&07 Wednesday/03:31 | Superuser |  NULL | NULL   |  NULL | NULL      |             1 |
+--------+-----------------+------------------------+-----------+-------+--------+-------+-----------+---------------+

Which is right but this only returns the first result of the grouped. So the quest stands, Is there any way to return all results of all groups in one query?

Now clearly I could just remove the GROUP BY and the count(*) phrase then get multiple duplicate results then filter them to get all details of the likers of the post but that would surely slow my servers down. So I've already tried that.

Any help would pretty much be greatly appreciated.

Answer 1

If your model looks like this

MariaDB [sandbox]> select * from posts;
+------+-----------+-----------+-------------+
| id   | post_body | post_type | from_userid |
+------+-----------+-----------+-------------+
|    1 | POST1     | NULL      |           1 |
|    2 | POST2     | NULL      |           2 |
+------+-----------+-----------+-------------+
2 rows in set (0.00 sec)

MariaDB [sandbox]> select * from likes;
+------+--------+-------+
| id   | TARGET | liker |
+------+--------+-------+
|    1 |      1 |     3 |
|    2 |      1 |     7 |
|    3 |      2 |     8 |
|    3 |      2 |     6 |
+------+--------+-------+
4 rows in set (0.00 sec)

MariaDB [sandbox]> select * from users where id < 9;
+----+----------+-----------+--------+---------------------+
| id | userName | photo     | status | ts                  |
+----+----------+-----------+--------+---------------------+
|  1 | John     | john.png  |      1 | 2016-12-08 13:14:24 |
|  2 | Jane     | jane.png  |      1 | 2016-12-08 13:14:24 |
|  3 | Ali      |           |      1 | 2016-12-08 13:14:24 |
|  6 | Bruce    | bruce.png |      1 | 2016-12-08 13:14:24 |
|  7 | Martha   |           |      1 | 2016-12-08 13:14:24 |
|  8 | Sidney   |           |      1 | 2016-12-08 13:14:24 |
+----+----------+-----------+--------+---------------------+
6 rows in set (0.00 sec)

Then as @1000111 suggests you can

MariaDB [sandbox]> SELECT  posts.id postid,posts.post_body,posts.post_type,POSTS.FROM_USERID
    ->  , USERS.USERNAME
    ->        ,GROUP_CONCAT(likes.liker) LIKER
    ->  ,likes.target
    ->  ,GROUP_CONCAT(plikers.pl_id) pl_id
    ->        ,GROUP_CONCAT(plikers.UNAME) pl_un
    ->  ,COUNT(posts.id) numberOflikes
    ->
    -> FROM posts
    -> INNER JOIN USERS ON USERS.ID=posts.FROM_userid
    -> LEFT JOIN likes ON likes.target=posts.id
    -> LEFT JOIN(SELECT ID pl_id, USERNAME UNAME FROM USERS )plikers ON pl_id=likes.liker
    -> GROUP BY postid;
+--------+-----------+-----------+-------------+----------+-------+--------+-------+--------------+---------------+
| postid | post_body | post_type | FROM_USERID | USERNAME | LIKER | target | pl_id | pl_un        | numberOflikes |
+--------+-----------+-----------+-------------+----------+-------+--------+-------+--------------+---------------+
|      1 | POST1     | NULL      |           1 | John     | 7,3   |      1 | 7,3   | Martha,Ali   |             2 |
|      2 | POST2     | NULL      |           2 | Jane     | 6,8   |      2 | 6,8   | Bruce,Sidney |             2 |
+--------+-----------+-----------+-------------+----------+-------+--------+-------+--------------+---------------+
2 rows in set (0.00 sec)

but you should be mindful of the caveat