繞過約束“列必須出現在 GROUP BY 子句中或在聚合函式中使用”

January 26, 2015

我正在使用 Postgres，它強制執行 SELECT…GROUP BY 中的所有列必須出現在 GROUP BY 子句中或在聚合函式中使用的約束。
假設我正在為人們的汽車建模，我想計算出一個人的姓名、駕照號碼以及他們擁有多少輛汽車。這是我作為SQL Fiddle的範例。
我將有以下架構：
CREATE TABLE person(
 id integer PRIMARY KEY,
 name text
);

CREATE TABLE license(
 person_id integer REFERENCES person(id),
 expiry_date date
);

CREATE TABLE car(
 owner_id integer REFERENCES person(id),
 registration_number TEXT
);
這是查詢：
SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
 JOIN license ON license.person_id = person.id
 JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.id;
我知道，由於我自己的業務邏輯，一個人只能有一個許可證，所以當我加入這個人時，即使是 GROUP BY’d，我應該能夠找到他們的許可證號。但是，因為它不是 GROUP BY 語句的一部分並且不使用聚合函式，所以我無法訪問它。
我應該如何以慣用的方式編寫此查詢。我已經看到關於 LATERAL JOIN、視窗函式和子查詢的模糊資訊，但我不確定回答這個問題的最佳方法。

應該是這樣的（應該是這樣的）：

SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
 JOIN license ON license.person_id = person.id
 JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.name, person.id, license.expiry_date, car.car;

你可以GROUP BY在你做任何加入之前……這意味著，扭轉你的查詢。
這是您的舊查詢
SELECT person.name, person.id, license.expiry_date, COUNT(car) FROM person
 JOIN license ON license.person_id = person.id
 JOIN car ON car.owner_id = person.id
WHERE person.name = 'Charles Bannerman'
GROUP BY person.id;
這是您的新查詢
WITH vals AS (
 SELECT owner_id, COUNT(car) AS cars FROM car GROUP BY owner_id
) SELECT
 person.name, person.id, license.expiry_date, vals.cars
FROM cars
JOIN person ON cars.owner_id = person.id
JOIN license ON person.id = license.person_id
WHERE person.name = 'Charles Bannerman';
該解決方案可能不像您想要的那樣有效……另一個潛在的解決方案是編寫一個 plpgsql 函式來在給定 person_id 時返回汽車數量。
我選擇的解決方案將取決於查詢的案例。查詢是否總是用於返回單行數據（針對特定人）？..還是會在報告中使用以顯示多行?..