实现没有窗口函数的 SQL 查询 [英] Implementing a SQL query without window functions
问题描述
我读到可以在 SQL 窗口函数中实现任何你可能做的事情,创造性地使用连接等,但我不知道如何实现.我在这个项目中使用 SQLite,它目前没有窗口函数.
I have read that it is possible to implement anything you might do in a SQL window function, with creative use of joins, etc, but I cannot figure out how. I'm using SQLite in this project, which doesn't currently have window functions.
我有一个包含四列的表格:
I have a table with four columns:
CREATE TABLE foo (
id INTEGER PRIMARY KEY,
x REAL NOT NULL,
y REAL NOT NULL,
val REAL NOT NULL,
UNIQUE(x,y));
以及返回两点之间距离的便捷函数 DIST(x1, y1, x2, y2).
and a convenience function DIST(x1, y1, x2, y2) that returns the distance between two points.
我想要的: 对于该表中的每一行,我希望该表中的整行都在一定距离内 [例如 25 公里],具有最低的val".对于具有相同val"的行,我想使用最低距离作为决胜局.
What I want: For every row in that table, I want the entire row in that same table within a certain distance [eg 25 km], with the lowest "val". For rows with the same "val", I want to use lowest distance as a tie breaker.
我当前的解决方案是运行 n+1 个查询,这可行但很糟糕:
My current solution is running n+1 queries, which works but is ucky:
SELECT * FROM foo;
...然后,对于返回的每一行,我运行[其中src"是我刚得到的行]:
... then, for each row returned, I run [where "src" is the row I just got]:
SELECT * FROM foo
WHERE DIST(foo.x, foo.y, src.x, src.y)<25
ORDER BY val ASC, DIST(foo.x, foo.y, src.x, src.y) ASC
LIMIT 1
但我真的希望在单个查询中使用它,部分是为了我自己的兴趣,部分是因为它可以更轻松地使用我拥有的其他一些工具.
But I really want it in a single query, partially for my own interest, and partially because it makes it much easier to work with some other tools I have.
推荐答案
使用您的查询获取所需行的 ID,然后使用它来连接表:
Use your query to get the ID of the wanted row, then use that to join the tables:
SELECT *
FROM (SELECT foo.*,
(SELECT id
FROM (SELECT id,
x,
y,
foo.x AS foo_x,
foo.y AS foo_y,
val
FROM foo)
WHERE DIST(foo_x, foo_y, x, y) < 25
ORDER BY val, DIST(foo_x, foo_y, x, y)
LIMIT 1
) AS id2
FROM foo)
JOIN foo AS foo2 ON id2 = foo2.id;
这篇关于实现没有窗口函数的 SQL 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!