如何检查当前行是否是sql查询中最后选择的行？ [英] How to check if the current row is the last selected row in a sql query?

问题描述

我有一个简短的问题！我选择的行数如下所示：

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

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

我想在结果集中添加另一个字段/属性，例如 last ，它确定当前行是否为结果集中的最后一行。我希望结果集如下所示吗？

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?

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

推荐答案

1. 您必须指定 ORDER BY 来定义行的排序。


2. 我相信任何针对MySQL的解决方案都将有效地意味着您的查询至少执行两次，因此在客户端上执行它确实更好。

1. You have to specify ORDER BY to define some sorting of the rows.
2. 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.

如果可以两次运行相同的查询，可以这样做。

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

获取ID最后一行优先：

Get the ID of the last row first:

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

在主查询中使用该ID：

Use that ID in the main query:

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

按原样，如果在运行这两个查询时更新表，它很可能会损坏（记住如果添加或删除行，则ID可能不是最后一个，除非您为解决此问题采取了某些措施。）

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).

当然，您可以将所有内容都放入一个查询中：

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;

您需要检查MySQL是否足够智能以运行内部 SELECT LIMIT 1 一次，否则它将在主表的每一行运行。即使在最佳情况下，查询也有效执行了两次。

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.

这篇关于如何检查当前行是否是sql查询中最后选择的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！