postgresql在groupby时，后面不需要跟所有需要查询的字段吗？

Question

我网上看到说，需要select 的字段，都得跟到group by 子句中，但是我在postgresql中试了以下两条命令，都没出错耶

select add_time, hope_price from bargain group by  id;

select buy.id, title, count("buy_id") from buy left join buy_have as t1 on t1.buy_id = buy.id group by buy.id;

id是数据库的primary key

Answer 1

貌似 MySQL 也不需要跟，但是查出来的是哪一个并不能确定，除非你所有记录的那个字段都是一样的就不存在这个问题。

Answer 2

SQL-2003 标准, 如果 select 字段 functionally dependent 在group by字段上, 则查询是 合法的.

你给的查询group by在主键上, 所以肯定合法. 以下见postpresql文档:
http://www.postgresql.org/docs/9.4/static/sql-select.html

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.

Mysql则自由的多, 最新的mysql版本, 如果在 ONLY_FULL_GROUP_BY 模式, 则符合 sql标准; 否则select字段可以和 group by字段完全没有关系:

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html