MYSQL将所有分组结果与行计数一一查询 [英] MYSQL get all grouped results one one query with a row count

问题描述

因此，我到处都在搜索此内容，但找不到这种特定的关系.下面的查询返回用户的所有帖子以及喜欢该帖子的人数，因为我使用了GROUP BY postid，这意味着，如果结果重复自己但具有相同的postid，则将它们分组.

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

结果是...

+--------+-----------------+------------------------+-----------+-------+--------+-------+-----------+---------------+
| 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?

现在，很明显，我可以先删除GROUP BY和count(*)短语，然后删除get multiple duplicate results，然后对其进行过滤，以获取该帖子的likers的所有详细信息，但这肯定会使我的服务器速度降低.所以我已经尝试过了.

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.

任何帮助将不胜感激.

推荐答案

如果您的模型看起来像这样

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)

然后以@ 1000111表示您可以

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)

但是您应该注意警告

这篇关于MYSQL将所有分组结果与行计数一一查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！