postgresql 聚合的暗坑

rudy_gao發表於2016-01-27
SQL
--對於聚合操作，pg約束是不嚴格的，比如如下sql中，group by 缺少 name，但也能執行
postgres=# select id,name ,count(*) from t group by id;
 id | name | count 
----+------+-------
  1 | bcd  |     1
  2 | abc  |     1
  
  
--現模擬如下  
create table t(id int,name varchar(20));
insert into t values(1,'abc'),(2,'bcd');   

--再次執行，不行了，說語法不對
postgres=# select id,name ,count(*) from t group by id;
ERROR:  column "t.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select id,name ,count(*) from t group by id;


--新增主鍵約束，則能直行成功，說明pg進行了智慧判斷,在有唯一約束的前提下，當select的非聚合欄位比如name是伴隨id成對出現的時候，則pg允許
--如下：因為id是唯一的，id與name也是唯一的（兩個欄位必須是在同一個表中），故pg允許
postgres=# alter table t add primary key(id);      
ALTER TABLE
postgres=# select id,name ,count(*) from t group by id;
 id | name | count 
----+------+-------
  1 | bcd  |     1
  2 | abc  |     1
  
  
--建立t1表  
create table t1(id int,name varchar(20));
insert into t1 values(1,'abc'),(2,'bcd');   
alter table t1 add primary key(id);  
--因為t.id是唯一的，但t.id與t1.name並不是唯一的（兩個欄位不在同一個表中），所以會把語法錯誤
postgres=# select t.id,t1.name from t1,t where t1.id=t.id group by t.id;     
ERROR:  column "t1.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select t.id,t1.name from t1,t where t1.id=t.id group by t.id...



--而對於mysql，當sql_mode不設定ONLY_FULL_GROUP_BY是，它並不限制group by欄位的完整性
mysql> select id,name ,count(*) from t group by id;
+------+------+----------+
| id   | name | count(*) |
+------+------+----------+
|    1 | abc  |        1 |
|    2 | bcd  |        1 |
+------+------+----------+
2 rows in set (0.02 sec)
--設定ONLY_FULL_GROUP_BY
mysql> set sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.11 sec)
--group by 語法不全規範，報錯
mysql> select id,name ,count(*) from t group by id;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.name' 
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

相關文章