postgresql 聚合的暗坑

  1. 云栖社区>
  2. 博客>
  3. 正文

postgresql 聚合的暗坑

rudy_gao 2016-01-27 14:51:58 浏览331
展开阅读全文
--对于聚合操作,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

网友评论

登录后评论
0/500
评论
rudy_gao
+ 关注