sql - 在sql中执行排序操作后获取行的偏移量

Question

我正在使用 SQLite 数据库。

假设我有 ID 为 1 到 50 的行。然后我执行 select 和 order by 操作。

比方说，结果是 IDs:6,3,5,2,9,12,1,34,45,15。

现在，我想知道上面结果中具有给定 ID 的特定行的偏移量。例如ID 1 的偏移量是 6。

我可以在单个查询中执行此操作吗？

Answer 1

将ordered 的查询结果放入子查询 并使用count(*) 并检查id 序列:

示例:

---

架构:

CREATE TABLE tbl ("id" INTEGER,"val" INTEGER);
INSERT INTO tbl ("id","val")
VALUES
    (12,6),(1,7),(34,8),(6,1),(9,5),
    (45,9),(15,10),(3,2),(5,3),(2,4);

查询:

select id,(
           select count(*) 
           from (
                 select id,val 
                 from tbl order by val
                 ) b  
           where a.val >= b.val)-1 as offset
from tbl a
order by offset

结果:

id  offset
6   0
3   1
5   2
2   3
9   4
12  5
1   6
34  7
45  8
15  9

SQLFIDDLE DEMO