在使用GROUP BY的時候，一般來說GROUP BY後跟分組列，但實際上在PG中可以在GROUP BY後跟表主鍵。

```
[local]:5014 pgmaster@testdb=# drop table t;
DROP TABLE
Time: 39.739 ms
[local]:5014 pgmaster@testdb=# create table t(id int primary key,name text,title text);
CREATE TABLE
Time: 59.905 ms
[local]:5014 pgmaster@testdb=# insert into t values(1,'test1','test1');
INSERT 0 1
Time: 5.222 ms
[local]:5014 pgmaster@testdb=# insert into t values(2,'test','test');
INSERT 0 1
Time: 5.223 ms
[local]:5014 pgmaster@testdb=# insert into t values(3,'test','test');
INSERT 0 1
Time: 4.569 ms
[local]:5014 pgmaster@testdb=# select name,count(*) from t group by id;
 name  | count
-------+-------
 test  |     1
 test  |     1
 test1 |     1
(3 rows)

Time: 8.932 ms
[local]:5014 pgmaster@testdb=# select name,count(*) from t group by name;
 name  | count
-------+-------
 test  |     2
 test1 |     1
(2 rows)

Time: 0.906 ms
[local]:5014 pgmaster@testdb=# select name,count(*) from t group by title;
2022-02-11 15:13:15.392 CST [1882] ERROR:  column "t.name" must appear in the GROUP BY clause or be used in an aggregate function at character 8
2022-02-11 15:13:15.392 CST [1882] STATEMENT:  select name,count(*) from t group by title;
ERROR:  column "t.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select name,count(*) from t group by title;
               ^
Time: 0.291 ms
[local]:5014 pgmaster@testdb=#

```

值得一提的是，在MySQL中，分組列不需要在GROUP BY中，在實際執行的時候MySQL會隨機選取GROUP BY分組後該列的一個隨機值返回。

**參考資料**
[Functional Dependencies in SQL GROUP BY](https://blog.jooq.org/functional-dependencies-in-sql-group-by/)
[group-by-handling](https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/group-by-handling.html)

PostgreSQL DBA(186) - SQL Group By

相關文章