--对于聚合操作,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