从 sqlite 表中检索排名 [英] Retrieve Rank from sqlite table

问题描述

假设我在 sqlite 数据库中有一个表，有两个字段:名称和年龄.

Say I have a table in an sqlite DB with two fields: name and age.

Bob|40
Rob|50
Zek|60

如何查询Zek的sqlite表并确定他是最老的?更一般地说，假设我有数百万个名字和年龄，我想查询一个特定的条目，比如 name="Juju bear"，并通过不同的字段找到条目的排名，例如那个Juju熊"排名133455(按年龄).

How can I query the sqlite table for Zek and determine that he is the oldest? More generally, say I have millions of names and ages and I want to query a specific entry, say name="Juju bear", and find the rank of the entry by a different field, e.g. that "Juju bear" is ranked 133455 (by age).

谢谢，

科罗拉多

推荐答案

你可以使用子查询来统计年龄较大的人数，比如:

You can use a subquery to count the number of people with a higher age, like:

select  p1.*
,       (
        select  count(*) 
        from    People as p2
        where   p2.age > p1.age
        ) as AgeRank
from    People as p1
where   p1.Name = 'Juju bear'

这篇关于从 sqlite 表中检索排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！