首页 文章

如何知道当前行是SQlite中的最后一行?

提问于
浏览
0

我正在使用Sqlite 3 .

在调用sqlite3_step()之前,我需要知道当前行是否是SQL查询结果中的最后一行 .

检查现有帖子后

SQLite3 - how to know if the current row is the last row

How to check if the current row is the last selected row in a sql query?

由于SQlite3中的每一行都有一个ROWID,我想出了一种编写代码的方法,如下所示:

SELECT (ROWID = MAX(ROWID)) AS IsLast FROM MyTable ORDER BY ROWID;

MyTable中的数据是:

|BID|
|3|
|2|
|5|
|7|

预期结果是除最后一行之外的所有行都为FALSE,如下所示:

|0|
    |0|
    |0|
    |1|

但实际结果是:

|1|

此外,我尝试将代码集成到更复杂的查询中,该查询来自SQL select only rows with max value on a column,如下所示:

SELECT a.id, a.rev, a.contents, b.mycount, (a.ROWID = MAX(a.ROWID)) AS IsLast
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev, COUNT(id) mycount
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev ORDER BY a.ROWID;

以原始帖子中的原始表格为例:

+------+-------+--------------------------------------+
        | id   | rev   | content                              |
        +------+-------+--------------------------------------+
        | 1    | 1     | ...                                  |
        | 2    | 1     | ...                                  |
        | 1    | 2     | ...                                  |
        | 1    | 3     | ...                                  |
        +------+-------+--------------------------------------+

使用查询后,预期结果应该是(最后一列是IsLast标志):

|2|1|...|1|0|
    |1|3|...|3|1|

但实际结果只有一行:

|1|3|...|3|1|

问题是什么?

谢谢

2 回答

  • 1

    您的子查询不包含 ROWID 列但您在主查询中使用此列导致错误 .

    您可以尝试使用子查询来获取 MAX(ROWID) 然后使用 CASE WHEN 来设置最后一个标志 .

    你的第一个查询

    Schema (SQLite v3.18)

    CREATE TABLE YourTable(
      BID int
    );
    
    INSERT INTO YourTable VALUES (3);
    INSERT INTO YourTable VALUES (2);
    INSERT INTO YourTable VALUES (5);
    INSERT INTO YourTable VALUES (7);
    

    Query #1

    SELECT  
         BID,(CASE WHEN (SELECT MAX(ROWID) FROM YourTable) = a.ROWID then 1 else 0 end) AS IsLast
    FROM YourTable a;
    
    | BID | IsLast |
    | --- | ------ |
    | 3   | 0      |
    | 2   | 0      |
    | 5   | 0      |
    | 7   | 1      |
    

    View on DB Fiddle

    Schema (SQLite v3.18)

    CREATE TABLE YourTable(
      Id int,
      rev int,
      contents varchar(50)
    );
    
    INSERT INTO YourTable VALUES (1,1,'test1');
    INSERT INTO YourTable VALUES (2,1,'test2');
    INSERT INTO YourTable VALUES (1,2,'test1');
    INSERT INTO YourTable VALUES (1,3,'test2');
    

    Query #1

    SELECT a.*,
           (CASE WHEN maxROWID = a.ROWID then 1 else 0 end) AS IsLast
    FROM YourTable a
     JOIN 
    (
      SELECT Id,
             MAX(rev) rev, 
             COUNT(id) mycount,
             (SELECT MAX(ROWID) FROM YourTable) maxROWID
      FROM YourTable
      group by Id
    ) b  ON a.id = b.id AND a.rev = b.rev
    ORDER BY a.ROWID;
    
    | Id  | rev | contents | IsLast |
    | --- | --- | -------- | ------ |
    | 2   | 1   | test2    | 0      |
    | 1   | 3   | test2    | 1      |
    

    View on DB Fiddle

  • 0

    文档说sqlite3_step()根据查询的状态返回不同的值

    https://www.sqlite.org/c3ref/step.html

    乍一看,看起来你应该调用它直到它返回SQLITE_DONE . 然后,您需要重置它才能在该语句上再次调用它

相关问题