設定
我有一張比賽時間表，列出了一名跑步者、他們的團隊和他們的比賽時間：
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";
它發出
| 人 | 排行 |
|---|---|
| 艾哈邁德 | 1 |
| 卡雷爾 | 2 |
| 法萊 | 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視窗函式來實作這一點嗎？就其本身而言，lag會查看符合我的WHERE team="red"標準的行，因此如果他們在綠色團隊或其他非紅色團隊中，它不會回傳下一個最慢的跑步者。
這種查詢有通用術語嗎？
可能有很多團隊和很多跑步者，所以我想知道如何使這種查找盡可能高效。
uj5u.com熱心網友回復：
用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;
演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/375465.html
上一篇：面試題61：撲克牌中的順子