Postgres数据库 Group by 特性

Postgres数据库中对于group by 子句遵守SQL 2011标准，要求select 字段中，除了分组字段外，其他字段都需要加上分组函数，如max,min等。

但对于分组字段中包含主键字段的场景例外，SQL-2003 标准, 如果 select 字段 functionally dependent 在group by字段上, 则查询是 合法的.

因此当group by 的字段包含了主键字段时，select 的字段可以包含非分组字段，且不需要使用分组函数。例如：

select m.mapping_id "mappingId", m.mapping_type "mappingType", m.mapping_value "mappingValue", 
m.enterprise_id "groupId", m.analysis_type "analysisType" from c4c_audit_mapping m where m.mapping_type 
= 'lastLogin' and m.enterprise_id in ( 1,2 ) and object_type = 'xxx' and object_id = 2 

group by m.enterprise_id,m.mapping_id;

　　其中 mapping_id 字段是主键

Postgres官方说明：

PostgreSQL: Documentation: 10: SELECT

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.