CTE 子句不能在最終的 ORDER BY 語句中使用

February 10, 2022

嘗試使用輸入幾何按距離排序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”，並且正在拋出這個錯誤，因為它們正在使用聚合函式

查詢的語法通常是正確的。
但是，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 的實例，這應該是受歡迎的副作用。