問(wèn)題描述

假設(shè)我有這個(gè)表:named = the_table其結(jié)構(gòu)為:

postgreSQL:

 create table the_table (col3 SERIAL, col2 varchar, col1 varchar, PRIMARY KEY(col3));

MySQL:

create table the_table ( col3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col2 varchar(20), col1 varchar(20) )

然后我插入了表格:

INSERT INTO the_table (col2,col1) VALUES('x','a'),('x','b'),('y','c'),('y','d'),('z','e'),('z','f');

現(xiàn)在表格如下所示:

col3 |col2 |第 1 列------+------+------1 |× |一種2 |× |乙3 |是 |C4 |是 |d5 || |電子6 || |F

當(dāng)我做這個(gè)查詢時(shí):

select * from the_table group by col2

然后在mysql中我得到:

1 x a3 年5澤

在 postgreSQL 中，我收到錯(cuò)誤:

錯(cuò)誤:列the_table.col3"必須出現(xiàn)在 GROUP BY 子句中或用于聚合函數(shù)中LINE 1: select * from the_table group by col2;

我的問(wèn)題:

這個(gè)錯(cuò)誤是什么意思?什么是聚合函數(shù)?

當(dāng)它在 MySQL 中工作時(shí)，為什么它不能在 postgreSQL 中工作?

您需要使用 聚合函數(shù):

聚合函數(shù)從一組輸入計(jì)算單個(gè)結(jié)果值.

SELECT col2, MIN(col3) AS col3, MIN(col1) AS col1FROM the_table按 col2 分組；

db<>小提琴演示<小時(shí)><塊引用>

MySQL 處理 GROUP BY:

在標(biāo)準(zhǔn) SQL 中，包含 GROUP BY 子句的查詢不能引用到選擇列表中未命名的非聚合列GROUP BY 子句

和:

MySQL 擴(kuò)展了 GROUP BY 的使用，以便選擇列表可以引用未在 GROUP BY 子句中命名的非聚合列.這意味著前面的查詢?cè)?MySQL 中是合法的.您可以使用此功能通過(guò)避免不必要的列排序和分組來(lái)獲得更好的性能.但是，這主要在未在 GROUP BY 中命名的每個(gè)非聚合列中的所有值對(duì)于每個(gè)組都相同時(shí)很有用.服務(wù)器可以自由地從每個(gè)組中選擇任何值，因此除非它們相同，否則選擇的值是不確定的

因此，對(duì)于沒(méi)有顯式聚合函數(shù)的 MySQL 版本，您最終可能會(huì)得到不確定的值.我強(qiáng)烈建議使用特定的聚合函數(shù).

來(lái)自 MySQL 對(duì) GROUP BY 的處理:

SQL92 及更早版本不允許查詢的選擇列表、HAVING 條件或 ORDER BY 列表引用未在 GROUP BY 子句中命名的非聚合列.

SQL99 和更高版本允許每個(gè)可選功能 T301 的此類非聚合如果它們?cè)诠δ苌弦蕾囉?GROUP BY 列:如果在 name 和 custid 之間存在這種關(guān)系，則查詢是合法的.例如，如果是客戶的主鍵，就會(huì)出現(xiàn)這種情況.

示例:

SELECT o.custid, c.name, MAX(o.payment)FROM 訂單 AS o加入客戶作為 cON o.custid = c.custidGROUP BY o.custid;

Suppose I have this table: named = the_table whose structure is:

postgreSQL:

 create table the_table (col3 SERIAL, col2 varchar, col1 varchar, PRIMARY KEY(col3));

MySQL:

create table the_table ( col3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col2 varchar(20), col1 varchar(20) )

Then I inserted the table:

INSERT INTO the_table (col2,col1) VALUES 
('x','a'),
('x','b'),
('y','c'),
('y','d'),
('z','e'),
('z','f');

Now the table looks like this:

col3 | col2 | col1 
------+------+------
    1 | x    | a
    2 | x    | b
    3 | y    | c
    4 | y    | d
    5 | z    | e
    6 | z    | f

When I do this query:

select * from the_table group by col2

then in mysql I get:

1 x a
3 y c
5 z e

and in postgreSQL, I am getting error:

ERROR:  column "the_table.col3" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select * from the_table group by col2;

My Questions:

What does this error mean? What is aggregate function ?

When it works in MySQL , why can't it work in postgreSQL ?

You need to use AGGREGATE FUNCTION:

Aggregate functions compute a single result from a set of input values.

SELECT col2, MIN(col3) AS col3, MIN(col1) AS col1
FROM the_table 
GROUP BY col2;

db<>fiddle demo

MySQL Handling of GROUP BY:

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause

and:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate

So with MySQL version without explicit aggregate function you may end up with undetermininistic values. I strongly suggest to use specific aggregate function.

EDIT:

From MySQL Handling of GROUP BY:

SQL92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.

SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.

Example:

SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o
JOIN customers AS c
  ON o.custid = c.custid
GROUP BY o.custid;

這篇關(guān)于mySQL 和 postgreSQL 中的 Group by 子句，為什么 postgreSQL 會(huì)出錯(cuò)?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！