MySQL如何决定在group by子句中返回哪个id？ [英] How does MySQL decide which id to return in group by clause?

问题描述

如此：

SELECT id，count（*），company
FROM jobs
GROUP BY公司

其中id是作业的主键

解决方案

相似的问题：

为什么MySQL允许group by查询WITHOUT聚合函数？

这是MySQL专用的，不是ANSI标准SQL。大多数其他的数据库引擎不允许没有被分组或者通过聚合函数运行的列。

看起来MySQL保留了第一行的值标准。

具有此行为的聚合函数是FIRST（），尽管MySQL实现它，但这似乎是未分组的列的默认行为，它们不会通过任何其他聚合函数运行。

在ANSI标准SQL中，您可以这样做：

  SELECT FIRST（jobtitle），company FROM jobs GROUP BY company; 
  

尽管在MySQL中你可以（但是ANSI标准也可以）：

  SELECT jobtitle，company FROM jobs GROUP BY company; 
  

Such as this one:

SELECT id, count( * ) , company FROM jobs GROUP BY company

Where id is the primary key of jobs

解决方案

Similar question:

Why does MySQL allow "group by" queries WITHOUT aggregate functions?

This is MySQL specific, and is not ANSI standard SQL. Most other database engines do not allow columns on which is not being grouped or being run through an aggregate function.

It seems MySQL retains the value of the first row that matches the criteria.

The aggregate function that has this behaviour is FIRST(), and although MySQL implements it, this seems to be default behaviour for columns that are not grouped on, and which are not run through any other aggregate function.

In ANSI standard SQL you would do:

SELECT FIRST(jobtitle), company FROM jobs GROUP BY company;

Whereas in MySQL you could just (however the ANSI standard works just aswell):

SELECT jobtitle, company FROM jobs GROUP BY company;

这篇关于MySQL如何决定在group by子句中返回哪个id？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！