区别 - 采取了哪些项目？第一次或最后一次出现？

If I use following query:

如果我使用以下查询:

SELECT DISTINCT comment FROM table;

And I have for example following data: (IDs are just there to SHOW the order...)

我有以下数据:( ID就在那里显示订单......)

ID | comment
-------------
1  | comment1
2  | comment1
3  | comment2
4  | comment1

What I could get back are following three results:

我能得到的是以下三个结果:

Result 1:

1  | comment1
3  | comment2

Result 2:

3  | comment2
4  | comment1

Result 3:

order is unpredicatable

Question 1:

Is the result independant from the platform? Can I make sure, that I always get a predictable result?

结果是否与平台无关?我可以确定,我总能获得可预测的结果吗?

Question 2:

I want to distinct select all comments and get the NEWEST only, meaning I want to always get result 2. Is it possible to achive that? Maybe ordering by the key would affect the result?

我想区分选择所有评论并仅获得最新评论,这意味着我希望始终获得结果2.是否有可能实现这一点?也许按键排序会影响结果?

3 个解决方案

#1

Your query doesn't request the ID column, only the comment column:

您的查询不会请求ID列,只会请求注释列:

SELECT DISTINCT comment FROM table;

In the result, the ID is not included, so the row each value comes from is irrelevant.

在结果中,不包括ID,因此每个值来自的行是无关紧要的。

comment1
comment2

As for how it will sort them, I think it depends on index order. I'll do a test to confirm:

至于如何对它们进行排序,我认为这取决于索引顺序。我会做一个测试来确认:

mysql> create table t (id int primary key, comment varchar(100));

mysql> insert into t values
    -> (1, 'comment2'),
    -> (2, 'comment1'),
    -> (3, 'comment2'),
    -> (4, 'comment1');

The default order is that of the primary key:

默认顺序是主键的顺序:

mysql> select distinct comment from t;
+----------+
| comment  |
+----------+
| comment2 |
| comment1 |
+----------+

Whereas if we have an index on the requested column, it returns the values in index order:

如果我们在请求的列上有索引,它将按索引顺序返回值:

mysql> create index i on t(comment);

mysql> select distinct comment from t;
+----------+
| comment  |
+----------+
| comment1 |
| comment2 |
+----------+

I'm assuming the InnoDB storage engine, because everyone should be using InnoDB. ;-)

我假设InnoDB存储引擎,因为每个人都应该使用InnoDB。 ;-)

Your last question indicates that you really want a query that doesn't involve DISTINCT at all, but it's a greatest-n-per-group question. This type of question is very common, and it has been asked and answered hundreds of times on *. Follow the link and read the many solutions.

你的上一个问题表明你真的想要一个完全不涉及DISTINCT的查询,但这是一个每组最大的问题。这类问题很常见,在*上已被问及并回答了数百次。点击链接阅读许多解决方案。

#2

You can experiment and see which of the unique rows is returned, and you can experiment and see which order they're returned in, but that will only show you how things turn out with your experimental table, today, under the current database engine version. Bottom line:

您可以尝试并查看返回哪些唯一行,并且您可以试验并查看它们返回的顺序,但这只会向您展示今天在当前数据库引擎版本下实验表的结果。底线:

If you SELECT DISTINCT comment the id is immaterial because it's not in your SELECT

如果你SELECT DISTINCT注释id是无关紧要的,因为它不在你的SELECT中

If you don't ORDER BY the database will determine the order.

如果没有ORDER BY,数据库将确定订单。

If you want the most recent distinct comment with its ID, this will work every time (full disclosure: this replaces an earlier answer that works but was over-thinking the problem):

如果你想要最新的不同评论及其ID,这将每次都有效(完全披露:这取代了早期的答案,但有点过分考虑问题):

SELECT comment, MAX(id)
FROM myTable
GROUP BY comment
ORDER BY 2 DESC;

Note that the ORDER BY 2 DESC assumes that the higher the ID, the more recent the comment.

请注意,ORDER BY 2 DESC假定ID越高,评论越新。

#3

If you select a single distinct column, the other will not be returned.

如果选择单个不同的列,则不会返回另一个列。

select distinct column from table

is the same result as

与...相同的结果

select column from table group by column

In both these cases, the sort order of column is unpredictable, depending on the execution plan which may vary with larger amounts of data, diferent table structures, diferent database versions

在这两种情况下,列的排序顺序是不可预测的,具体取决于执行计划,该计划可能随着大量数据,不同的表结构,不同的数据库版本而变化

to mimic your result, one would have to do :

为了模仿你的结果,人们必须这样做:

select id, column from table group by column

which is an illegal grouping. If your SQL mode permits it to run, ID will be random.

这是一个非法的分组。如果您的SQL模式允许它运行,则ID将是随机的。

if you mean select distinct * from table, then all distinct rows will be returned, in your case all the table.

如果你的意思是从表中选择distinct *,那么将返回所有不同的行,在你的情况下是所有表。

#1