自加入表時聚合函式的使用

我想從數據庫中找到體重大於平均體重的人：

SELECT *
FROM Player p1, Player p2
WHERE p1.weight > avg(p2.weight)

但我有一個錯誤：

Result: misuse of aggregate function avg()

我知道我可以寫：

SELECT *
FROM Player
WHERE weight > (SELECT AVG(weight)
               FROM Player)

但是我怎樣才能通過使用自連接而不使用子查詢來實現同樣的效果呢？

假設一個像這樣的表：

CREATE TABLE player
   ( player_id int not null primary key
   , weight int not null
   , nationality char(2) not null -- just any additional attribute
   );

insert into player (player_id, weight, nationality)
values (1,70,'SE'), (2,75,'RU'), (3,60,'US');    

以下查詢似乎在 sqllite 中工作（據我所知，這是有效的 SQL99，因為 p1.weight、p1.nationality 在功能上依賴於 p1.player_id。大多數供應商仍然對 group by 實施更嚴格的 SQL92 規則）。另請注意，我更喜歡 ansi join 而不是 “,” 連接，我發現它們更易於閱讀。

select p1.player_id, p1.weight, p1.nationality
from player p1
cross join player p2
group by p1.player_id
having p1.weight > avg(p2.weight);

擁有隻是聊天者的語法糖：

select player_id, weight, nationality
from (
   select p1.*, avg(p2.weight) as avg_weight
   from player p1
   cross join player p2
   group by p1.player_id
) as t 
where weight > avg_weight;

另一種變體是加入派生表，與原始表幾乎相同的查詢：

select p1.*
from player p1
cross join (
 select avg(weight) as avg_weight
 from player
) as p2 
where p1.weight > p2.avg_weight;

另一方面，您可以使用視窗函式來實現相同的目的：

select player_id, weight, nationality
from (
   select p.*
        , avg(weight) OVER () as avg_weight 
   from player p
)
where weight > avg_weight;

您可以在以下位置找到範例：db-fiddle

這是使用子句的一個很好的例子WITH，也稱為CTEs - or COMMON TABLE EXPRESSIONS。話雖如此，您的工作查詢沒有任何問題！

您無法避免在這裡使用聚合 - 必須計算平均值，但我希望下面是使用 a 的一個很好的例子CTE- 即使在這種情況下不需要它。

事實上，CTEs 從來都不是絕對 必需的，它們只是一種優雅的速記，可以將子查詢從主查詢的主體中取出，真正有助於提高可讀性，從而減少錯誤（和壓力！）。

為了解決這個問題，我做了以下（dbfiddle）：

CREATE TABLE player (id INTEGER, weight INTEGER);

然後是一些簡單的數據：

INSERT INTO player VALUES (1, 60), (2, 70), (3, 80), (4, 90);

然後，查詢：

WITH wa AS
(
 SELECT AVG(weight) AS w_avg FROM player
)
SELECT * FROM player WHERE weight > (SELECT w_avg FROM wa);

這給出了（正確的）結果：

id  weight
3      80
4      90

CTEs 是一種非常強大的簡化查詢的方法，非常值得放入您的工具箱！ps 歡迎來到論壇！:-)

引用自：https://dba.stackexchange.com/questions/239638