sql - 在没有窗口函数的情况下实现 SQL 查询

Question

我读到过，通过创造性地使用连接等，可以在 SQL 窗口函数中实现任何你可能做的事情，但我不知道如何实现。我在这个项目中使用的是 SQLite，它目前没有窗口函数。

我有一个包含四列的表格:

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)。

我想要什么:对于该表中的每一行，我希望同一表中的整行都在一定距离内 [例如 25 公里]，具有最低的“val”。对于具有相同“val”的行，我想使用最低距离作为决胜局。

我当前的解决方案是运行 n+1 个查询，它可以工作但是很糟糕:

SELECT * FROM foo;

... 然后，对于返回的每一行，我运行 [其中“src”是我刚得到的行]:

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

但我真的希望在单个查询中使用它，部分是出于我自己的兴趣，部分是因为它使我可以更轻松地使用我拥有的其他一些工具。

Answer 1

使用您的查询获取所需行的 ID，然后使用它来连接表:

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;