实现没有窗口函数的 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屋！