Android环境下无ROW_NUMBER的SQLite按指定排序生成行号

我有一个存储字符串值的表，排序规则为先按字符串长度降序，再按字符串值降序。示例SQL如下：

CREATE TABLE t(v VARCHAR(255));

INSERT INTO t(v) VALUES ('100'), ('10'), ('8'), ('5'), ('3'), ('1');

SELECT v FROM t ORDER BY LENGTH(v) DESC, v DESC;

该查询的正确结果为：

100
10
8
5
3
1

我需要为上述排序后的每行生成对应的行号，但当前使用的Android环境中的SQLite版本不支持ROW_NUMBER()或其他窗口函数。请问仅用纯SQL能否实现？

我尝试了如下方案：

SELECT t1.v,
       (SELECT COUNT(*) FROM t t2 WHERE LENGTH(t1.v) <= LENGTH(t2.v) AND t1.v <= t2.v) - 1 AS rownum
  FROM t t1;

及类似的关联查询：

SELECT t1.v, COUNT(t2.v) - 1 AS rownum
FROM t t1
JOIN t t2 ON LENGTH(t1.v) <= LENGTH(t2.v) AND t1.v <= t2.v
GROUP BY t1.v
ORDER BY LENGTH(t1.v) DESC, t1.v DESC;

但得到的结果不符合预期：

100|0
10|1
8|0
5|1
3|2
1|5

这也是本问题与类似问题不重复的原因。

可以实现，核心是要准确匹配你的排序规则来统计当前行之前的记录数。

子查询写法（推荐） #

SELECT 
  t1.v,
  -- 统计所有排在当前行之前的记录数，加1得到从1开始的行号
  (SELECT COUNT(*) 
   FROM t t2 
   WHERE (LENGTH(t2.v) > LENGTH(t1.v)) 
      OR (LENGTH(t2.v) = LENGTH(t1.v) AND t2.v > t1.v)) + 1 AS rownum
FROM t t1
ORDER BY LENGTH(t1.v) DESC, t1.v DESC;

关联查询写法 #

SELECT 
  t1.v,
  COUNT(t2.v) + 1 AS rownum
FROM t t1
LEFT JOIN t t2 
  ON (LENGTH(t2.v) > LENGTH(t1.v)) 
  OR (LENGTH(t2.v) = LENGTH(t1.v) AND t2.v > t1.v)
GROUP BY t1.v
ORDER BY LENGTH(t1.v) DESC, t1.v DESC;

结果验证 #

执行上述查询后，会得到符合预期的结果：

100|1
10|2
8|3
5|4
3|5
1|6

如果需要行号从0开始，去掉查询中的+1即可。

原方案错误原因 #

你之前使用的LENGTH(t1.v) <= LENGTH(t2.v) AND t1.v <= t2.v是逻辑与条件，这会错误统计到那些长度更大但值更小的记录（比如8和100比较时，8 <= 100为真，会被误统计）。正确的条件应该用逻辑或来对应排序规则：要么长度更长，要么长度相同但值更大，这样才能准确统计出排在当前行之前的记录数量。

内容的提问来源于stack exchange，提问作者Nik