找出SQL中一组项目的最后一项

Question

想象一个这样的MySql表：

---------------------------
ListID | itemID | Item
---------------------------
List_1 | item_1 | Apple
---------------------------
List_1 | item_2 | Orange
---------------------------
List_1 | item_3 | Pear
---------------------------
List_2 | item_1 | Potatoes
---------------------------
List_2 | item_2 | ...

我有一个getNextItem()函数，该函数递增itemID并使用SQL查询来获取下一项：

SELECT * FROM items_tbl
WHERE listID = "$listId"
AND itemID = "$itemID"
LIMIT 1

在SQL中有没有一种简单的方法来判断我得到的项目是否是列表中的最后一项？

我可以向表中添加一列并标记每个列表的最后一项，或者我可以进行另一个查询，获取最高的项号并与我的列表中的当前项进行比较，或者获取列表中项的总数并跟踪与列表总数相关的项数……但所有这些解决方案看起来都像是一个黑客。

有没有人能用SQL推荐一些更好的东西，并解释一下它是如何工作的？

Answer 1

您没有说明您的数据库管理系统，但下面是一个符合ANSI (应该适用于PosgreSQL、Oracle、DB2)

SELECT *
FROM (
    SELECT listid, 
           itemid,
           case 
              when lead(itemid) over (partition by listid order by itemid) is null then 'last'
              else 'not_last'
           end as last_flag
    FROM items_tbl
    WHERE listID = 'List_1'
) t
WHERE itemID = 'item_2'

编辑，下面的代码应该可以在SQL Server上运行(因为它还不支持lead())：

SELECT listid, 
       itemid,
       case 
         when rn = list_count the 'last'
         else 'not_last'
       end
FROM (
    SELECT listid, 
           itemid,
           row_number() over (partition by listid order by itemid) as rn,
           count(*) over (partition by listid) as list_count
    FROM items_tbl
    WHERE listID = 'List_1'
) t
WHERE itemID = 'item_2'

Answer 2

我会将一个名为id的自动增量主键添加到您的表中，并使用嵌套SQL查询来获得表的最大id

SELECT id, ListID, itemID, item, (SELECT MAX(id) FROM table_name) as max_id from table_name;

这将返回表中的所有列以及名为max_id且具有最大id的附加列，因此您可以在应用程序中检查max_id == id，如果是，则它是最后一列

Answer 3

最简单的事情可能是，在开始之前，获取每个列表中最高的id：

Select listID, max(itemID) from mytable group by listID