主页 / dba / 问题 / 307320
Accepted
Underwater_developer
Asked: 2022-02-11 11:00:59 +0800 CST

CTE 子句不能在最终的 ORDER BY 语句中使用

  • 772

尝试使用输入几何按距离排序t(x)，同时与 JOINed 表中的几何进行比较。

WITH "nearest_groups" as (
    SELECT groups.id, ST_Distance(t.x, locations.center) AS nearest
    FROM (SELECT ST_GeographyFromText('SRID=4326;POINT(-121.0611 39.2191)')
    ) AS t(x), groups
    INNER JOIN locations ON groups.location_id = locations.id
    WHERE ST_DWithin(t.x, locations.center, 300000)
) 
SELECT *
FROM "groups"
INNER JOIN "nearest_groups" ON "groups"."id" = "nearest_groups"."id" 
ORDER BY "nearest_groups"."nearest" asc

错误：列“nearest_groups.nearest”必须出现在 GROUP BY 子句中或用于聚合函数*

我不明白错误度量需要我做什么才能使这个查询工作。在那里扔一个 GROUP BY 有意义吗？我也不熟悉聚合函数。

（！！！）查询似乎在 PSQL 中运行良好，但在我们的 ORM 环境（bookshelfjs/Knex）中却不行。我觉得这令人震惊；ORM 给了我永远存在的恐惧，我将不得不与他们搏斗，让他们做我想做的事

更新：我们正在使用 GraphQL，一个常见的模式是获取可以通过添加hasMore布尔值和total计数来分页的项目。所以在其他地方，正在编译“hasMore”和“total”，并且正在抛出这个错误，因为它们正在使用聚合函数

postgresql aggregate

1 个回答

  1. Best Answer

    查询的语法通常是正确的。

    但是，GROUPS是标准 SQL 中的保留字（至少在 SQL:2016 和 SQL:2011 中）。但是在 Postgres 中是“非保留的”。

    这可以解释为什么查询在 psql 中有效（与 Postgres 服务器对话！），但在 GraphQL 中无效。

    不要使用保留字作为标识符。如果您坚持自己的选择，则必须始终将名称双引号，例如"groups".

    除了 1

    Postgres 在最近的版本中减少了保留字的数量（并努力将数量保持在最低限度）。但是标准 SQL 仍然有保留（过多）单词的习惯，许多 RDBMS 也随之而来。对于“可移植”的 SQL 代码（它从来都不是真正可移植的），避免使用任何和所有保留字，即使是 Postgres 接受的那些。

    除了 2

    假设"groups".id已定义UNIQUE NOT NULL（如 PK），查询可以更简单、更便宜：

    SELECT g.*, st_distance(t.x, l.center) AS nearest
FROM  (SELECT ST_GeographyFromText('SRID=4326;POINT(-121.0611 39.2191)')) AS t(x)
     , "groups"  g
JOIN   locations l ON g.location_id = l.id
WHERE  ST_DWithin(t.x, l.center, 300000)
ORDER  BY nearest;

    结果中只有一个"groups".idis 的实例，这应该是受欢迎的副作用。

    • 2

相关问题