android - Mysql查询对应的Sqlite查询语法

Question

请帮助我找到与以下查询等效的 sqlite。尝试了很多搜索。

    SET @rownum := 0;
SELECT * FROM (
SELECT @rownum := @rownum+1 AS rank, id
FROM tbl_flight
ORDER BY id DESC
) AS tbl_flight WHERE id = 2

Answer 1

您不能在单个查询中执行此操作，但您可以使用临时表和每个表的 rowid 属性:

http://sqlfiddle.com/#!5/f0a1b/8 (你不能运行 fiddle 两次，你必须在再次运行之前总是先重建模式)

DROP TABLE IF EXISTS tmp;

CREATE TEMPORARY TABLE tmp AS 
  SELECT id
  FROM tbl_flight
  ORDER BY id DESC;

SELECT tmp.rowid AS rank, tmp.id FROM tmp WHERE id=2;