显示最后一个评论，每个用户只有1条评论 [英] show last comment which just 1 comment per user

问题描述

我有一个普通的注释表:

I have a normal comments table:

| id | comment | date | user | post | status |

| id | comment | date | user | post | status |

我想获取每条用户只有1条评论的最后10条评论，我的意思是这样的:

I want to fetch my 10 last comments with just 1 comment per user, I mean something like this:

我有以下数据: | id | comment | date | user | post | | 1 | text1 | 2016-04-01| 1 | 1 | | 2 | text2 | 2016-04-02| 2 | 1 | | 3 | text3 | 2016-04-03| 1 | 2 | | 4 | text4 | 2016-04-04| 4 | 3 | | 5 | text5 | 2016-04-04| 3 | 5 | | 6 | text6 | 2016-04-05| 2 | 4 | | 7 | text7 | 2016-04-07| 5 | 3 | | 8 | text8 | 2016-04-10| 4 | 9 | | 9 | text9 | 2016-04-11| 3 | 7 | | 10 | text10 | 2016-04-12| 5 | 8 |

I have this data: | id | comment | date | user | post | | 1 | text1 | 2016-04-01| 1 | 1 | | 2 | text2 | 2016-04-02| 2 | 1 | | 3 | text3 | 2016-04-03| 1 | 2 | | 4 | text4 | 2016-04-04| 4 | 3 | | 5 | text5 | 2016-04-04| 3 | 5 | | 6 | text6 | 2016-04-05| 2 | 4 | | 7 | text7 | 2016-04-07| 5 | 3 | | 8 | text8 | 2016-04-10| 4 | 9 | | 9 | text9 | 2016-04-11| 3 | 7 | | 10 | text10 | 2016-04-12| 5 | 8 |

，我想得到这个: | 10 | text10 | 2016-04-12| 5 | 8 | | 9 | text9 | 2016-04-11| 3 | 7 | | 8 | text8 | 2016-04-10| 4 | 9 | | 6 | text6 | 2016-04-05| 2 | 4 | | 3 | text3 | 2016-04-03| 1 | 2 |

and I want get this : | 10 | text10 | 2016-04-12| 5 | 8 | | 9 | text9 | 2016-04-11| 3 | 7 | | 8 | text8 | 2016-04-10| 4 | 9 | | 6 | text6 | 2016-04-05| 2 | 4 | | 3 | text3 | 2016-04-03| 1 | 2 |

我使用以下sql语句:

I use this sql statement:

SELECT  *
FROM  `comments`
GROUP BY  `user` 
ORDER BY MAX(`id`) DESC

这将以用户正确的顺序获取评论，但以评论日期的错误顺序获取评论: | 7 | text7 | 2016-04-07| 5 | 3 | | 9 | text9 | 2016-04-11| 3 | 7 | | 4 | text4 | 2016-04-04| 4 | 3 | | 6 | text6 | 2016-04-05| 2 | 4 | | 1 | text1 | 2016-04-01| 1 | 1 |

This fetches comments with correct order for user but wrong order for comment's date: | 7 | text7 | 2016-04-07| 5 | 3 | | 9 | text9 | 2016-04-11| 3 | 7 | | 4 | text4 | 2016-04-04| 4 | 3 | | 6 | text6 | 2016-04-05| 2 | 4 | | 1 | text1 | 2016-04-01| 1 | 1 |

推荐答案

为什么不适用于GROUP BY

SELECT *不能与GROUP BY一起使用；它是无效的SQL. GROUP BY不选择表行.它使用提供的表达式创建行组，然后从每个组中生成一条新记录，并使用表达式中涉及的值计算该新记录的每一列.

Why it doesn't work with GROUP BY

SELECT * cannot be used with GROUP BY; it is invalid SQL. GROUP BY doesn't select table rows. It creates groups of rows using the provided expressions then, from each group, it generates a new record and computes each column of this new record using the values involved in the expression.

SELECT子句中出现的列必须满足以下规则之一:

The columns that appear in the SELECT clause must satisfy one of the following rules:

• 也出现在GROUP BY子句中；
• 与 GROUP BY聚合函数一起使用;
• 在功能上取决于GROUP BY子句中出现的列.

• also appear in the GROUP BY clause;
• are used with GROUP BY aggregate functions;
• are functionally-dependent on the columns that appear in the GROUP BY clause.

*是查询所用表的所有列名的快捷方式，而对于您的查询，只有user列满足上述要求之一.

While * is a shortcut for all the column names of the table(s) used by the query, for your query only the user column satisfy one of the requirements above.

MySQL 5.7.5之前的版本 MySQL没有执行上面的第三条规则.它过去接受SELECT子句列中包含的不符合任何GROUP BY要求的查询.查询针对此类列返回的值为不确定.

Before version 5.7.5 MySQL didn't implement the third rule above. It used to accept queries that contain in the SELECT clause columns that do not follow any of the GROUP BY requirements. The value returned by the query for such columns was indeterminate.

从5.7.5版开始，MySQL拒绝确实满足要求的GROUP BY查询.

Since version 5.7.5, MySQL rejects the GROUP BY queries that do satisfy the requirements.

无论哪种方式，解决问题的方法都不涉及GROUP BY.可以使用LEFT JOIN在正确的条件下轻松完成此操作:

Either way, the solution for your problem doesn't involve GROUP BY. It can be easily accomplished using a LEFT JOIN with the correct conditions:

SELECT lc.*
FROM comments lc               # 'lc' from 'last comment'
    LEFT JOIN comments nc      # 'nc' from 'newer comment'
        ON lc.user = nc.user   # both comments belong to the same user
        AND lc.id < nc.id      # 'nc' is newer than 'lc'
WHERE nc.id IS NULL            # there is no 'newer comment'
ORDER BY lc.id DESC
LIMIT 10

工作原理

它将表comments与自身连接起来，别名为lc(用户的最后注释"中的"lc")，别名为nc(表述为较新的注释"中的"nc"). join子句将lc的每个条目与属于同一用户(lc.user = nc.user)且较新的lc.id < nc.id的所有nc条目进行匹配；我假设ID是按顺序分配的，并且较新的注释具有更大的值id).

How it works

It joins the table comments, aliased as lc ("lc" from the "last comment" of a user) against itself, aliased as nc ("nc" from "newer comment"). The join clause matches each entry of lc with all entries of nc that belong to the same user (lc.user = nc.user) and are newer (lc.id < nc.id; I assumed the IDs are assigned sequentially and newer comments have larger values for id).

使用LEFT JOIN可以确保lc的每一行都出现在联接的结果中，即使在nc中找不到匹配的行(因为同一用户没有更新的注释).在这种情况下，使用NULL代替nc的字段. WHERE子句仅将在nc.id中具有NULL的行保留在最终结果集中；这意味着在lc部分中，它们包含每个用户的最新评论.

The use of LEFT JOIN ensures that every row of lc appears in the result of the join, even when no matching row is found in nc (because there is no newer comment of the same user). In this case, NULL is used instead of the fields of nc. The WHERE clause keeps in the final result set only the rows that have NULL in nc.id; this means in the lc part they contain the most recent comment of each user.

SELECT子句包含lc的所有字段(无论如何，nc的那些都是NULL). ORDER BY子句可用于对结果集进行排序. ORDER BY lc.id DESC将最新的注释放在首位，而LIMIT子句将结果集保持在适当的大小.

The SELECT clause contains all the fields of lc (those of nc are all NULL, anyway). The ORDER BY clause can be used to sort the result set. ORDER BY lc.id DESC puts the most recent comments first and the LIMIT clause keeps the result set to a decent size.

这篇关于显示最后一个评论，每个用户只有1条评论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！