如何检查当前行是否是sql查询中的最后选定行?

I have a short question! I am selecting a number of rows as simple as the following:

我有一个简短的问题!我选择的行数很简单，如下所示:

SELECT * FROM ... WHERE ... GROUP BY ...

I would like to add another field/attribute to results sets, let's say last, which determines if the current row is the last one in the results set. I would like the results set look like the following?

我想向结果集添加另一个字段/属性，比如last，它确定当前行是否是结果集中的最后一行。

id    name   last
---   ----   ----
12    foo    false
20    smth   false
27    bar    true

2 个解决方案

#1

You have to specify ORDER BY to define some sorting of the rows.
必须通过定义行排序来指定顺序。
I believe any solution for MySQL would effectively mean that your query is executed at least twice, so it is really better to do it on the client.
我相信任何MySQL解决方案都会有效地意味着您的查询至少执行两次，所以最好在客户机上执行。

If you are OK with running the same query twice, then do something like this.

如果您对运行相同的查询两次没有问题，那么执行如下操作。

Get the ID of the last row first:

首先获取最后一行的ID:

SELECT @LastID := ID 
FROM ... 
WHERE ... 
GROUP BY ...
ORDER BY ID DESC
LIMIT 1;

Use that ID in the main query:

在主查询中使用该ID:

SELECT * 
    , CASE WHEN ID = @LastID THEN FALSE ELSE TRUE END AS IsLast
FROM ... 
WHERE ... 
GROUP BY ...
ORDER BY ID ASC;

As is, most likely it will break if the table is updated while these two queries run (the remembered ID may become not the last one if rows are added or deleted, unless you do something to address this issue).

实际上，如果在这两个查询运行时更新了表，它很可能会中断(如果添加或删除了行，则记住的ID可能不会成为最后一个ID，除非您做了一些处理此问题的工作)。

Of course, you can put it all into one query:

当然，你可以把它全部放在一个查询中:

SELECT 
    *, 
    CASE WHEN ID = LastID THEN FALSE ELSE TRUE END AS IsLast
FROM 
    YourTable
    CROSS JOIN 
    (
        SELECT ID AS LastID
        FROM YourTable
        ORDER BY ID DESC
        LIMIT 1
    ) AS TableLastID
ORDER BY ID ASC;

You need to check whether MySQL is smart enough to run the inner SELECT LIMIT 1 only once or it will run it for every row of the main table. Still, even in the best case the query is effectively executed twice.

您需要检查MySQL是否足够智能，只运行一次内部SELECT LIMIT 1，否则它将对主表的每一行运行它。尽管如此，即使在最好的情况下，查询也会被有效地执行两次。

#2

You need to have a sort order to make the last record determinate. Using either the MAX() function for ASC sort order or MIN() for DESC you can test for equality to determine if the value of the sort order field is that of the last record in the resultset, at least when the sort field is a unique key. For example, in your query it looks like the sort order is on the ID field, which would normally be a unique key.

你需要一个排序顺序来确定最后一个记录。使用ASC排序顺序的MAX()函数或DESC的MIN()函数可以测试等式，以确定排序顺序字段的值是否为resultset中的最后一条记录，至少当排序字段是唯一的键时是如此。例如，在您的查询中，看起来排序顺序位于ID字段上，而ID字段通常是唯一的键。

SELECT id, field1, field2, id=MAX(id) AS last_record FROM my_table ORDER BY id ASC;

通过id ASC从my_table顺序中选择id、field1、field2、id=MAX(id)作为last_record;

#1

You have to specify ORDER BY to define some sorting of the rows.
必须通过定义行排序来指定顺序。
I believe any solution for MySQL would effectively mean that your query is executed at least twice, so it is really better to do it on the client.
我相信任何MySQL解决方案都会有效地意味着您的查询至少执行两次，所以最好在客户机上执行。