我读到可以在 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 查询
发布评论