代码之家 › 专栏 › 技术社区 › Ahmed Fasih

查找SQL/SQLite中WHERE子句省略的查询的滞后行

sqlite sql

0

Ahmed Fasih · 技术社区 · 4 年前

安装程序

CREATE TABLE race (person TEXT, team TEXT, timer FLOAT);

INSERT INTO race
  (person, team, timer)
VALUES
  ("ahmed", "red", 4.3),
  ("baadur", "green", 4.4),
  ("carel", "red", 4.5),
  ("dada", "green", 4.9),
  ("eder", "green", 5.0),
  ("farai", "red", 5.1);

我可以把名单上所有的人都列出来团队及其排名:

SELECT person, ROW_NUMBER() OVER(ORDER BY timer) AS ranking FROM race WHERE team="red";

发出

人	排名

卡雷尔
法莱	3.

问题

我还想知道跑步者的名字跟着

人	排名	下一个跑步者
艾哈迈德	1.
卡雷尔	2.
法莱	3.	无效的

注意,由于没有人的时间比Farai慢,Farai的第三列是空的。

我可以通过一个查询高效地完成这项工作吗?

我希望避免先通过一个查询获得红色跑步者的名单和他们的时间,然后再进行另外三个查询(或者更一般地说) N )查询以获取下一次的跑步者,例如,这就是我所做的想做什么:

SELECT person FROM race WHERE timer>=4.3 AND person != "ahmed" LIMIT 1;
SELECT person FROM race WHERE timer>=4.5 AND person != "carel" LIMIT 1;
SELECT person FROM race WHERE timer>=5.1 AND person != "farai" LIMIT 1;
-- ð

我可能可以将上述方法修改为单个查询,而不是多个单独的查询,但我觉得如果有一种方法可以为匹配查询的每一行运行子查询,那么应该可以在单个查询中获得所需的内容 WHERE team="red" 子句来查找下一行(可以通过 timer

例如,我可以使用SQLite的 lag 窗口功能能实现这一点吗?就其本身而言, 缓慢移动 会看到符合我要求的行 WHERE team=“red”

这种查询有一个通用术语吗?

可能会有很多团队和很多跑步者,所以我想知道如何使这种查找尽可能有效。

1 回复 | 直到 4 年前

1

Tim Biegeleisen 4 年前

使用 LAG ROW_NUMBER 带分区:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY team ORDER BY timer) ranking,
              LEAD(person) OVER (ORDER BY timer) next_runner
    FROM race
)

SELECT person, ranking, next_runner
FROM cte
WHERE team = 'red'
ORDER BY ranking;

查找SQL/SQLite中WHERE子句省略的查询的滞后行

安装程序

问题

Demo