天天做爱网站,欧美后门菊门交3p、,青草福利在线视频,久久久久久成人无码,小黄片视频在线播放,中国学生妹黄色一级片免费看,麻豆操逼视频,人成视频在线免费观看

分組查詢關鍵字group by通常和集合函數（MAX、MIN、COUNT、SUM、AVG）一起使用，它可以對一列或者多列結果集進行分組。例如要統計超市水果的種類，需要用count函數，要統計哪個水果價格最高，要用MAX（）函數。

一般情況下，我們在使用group by的時候，select中的列都要出現在group by中，比如select id,name,age from tuser group by id,name,age，那么我們是不是都要嚴格按照這種模式來寫sql呢？下面我們來一起探索下。

數據準備

創(chuàng)建一張學生表

CREATE TABLE `student1` (  `id` int(11) NOT NULL COMMENT '學號',  `name` varchar(60) NOT NULL COMMENT '姓名',  `birth` date NOT NULL COMMENT '出生日期',  `sex` varchar(1) DEFAULT NULL,  `age` int(11) NOT NULL,  `score` int(11) NOT NULL,  PRIMARY KEY (`id`))

插入數據

insert into student values(1,'Tom','1998-10-01','男',23,96),(2,'Jim','1997-07-04','男',24,95),(3,'Lily','1999-11-12','女',21,99),(4,'Lilei','1996-09-21','男',25,90),(5,'Lucy','1999-12-02','女',21,93),(6,'Jack','1988-04-27','男',32,89),(7,'Liam','1991-09-08',' 男',28,100);

數據展示

mysql> select * from student;+----+-------+------------+------+-----+-------+| id | name  | birth      | sex  | age | score |+----+-------+------------+------+-----+-------+|  1 | Tom   | 1998-10-01 | 男   |  23 |    96 ||  2 | Jim   | 1997-07-04 | 男   |  24 |    95 ||  3 | Lily  | 1999-11-12 | 女   |  21 |    99 ||  4 | Lilei | 1996-09-21 | 男   |  25 |    90 ||  5 | Lucy  | 1999-12-02 | 女   |  21 |    93 ||  6 | Jack  | 1988-04-27 | 男   |  32 |    89 ||  7 | Liam  | 1991-09-08 | 男   |  28 |   100 |+----+-------+------------+------+-----+-------+7 rows in set (0.00 sec)

測試驗證

1. select中的列都出現在group by中，通過下面的結果可以看出是可以正常執(zhí)行的。

mysql> select id,name,score from student where score >95  group by id,name,score;+----+------+-------+| id | name | score |+----+------+-------+|  1 | Tom  |    96 ||  3 | Lily |    99 ||  7 | Liam |   100 |+----+------+-------+3 rows in set (0.01 sec)

2. group by中只保留score或者name

mysql> select id,name,score from student where score >95  group by score;ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql> select id,name,score from student where score >95  group by name;ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.id' which is not functionally dependent on columnsin GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

3. group by中只保留id

mysql> select id,name,score from student where score >95  group by id;+----+------+-------+| id | name | score |+----+------+-------+|  1 | Tom  |    96 ||  3 | Lily |    99 ||  7 | Liam |   100 |+----+------+-------+3 rows in set (0.00 sec)

通過這個實驗可以看出group by中只保留id是可以正常執(zhí)行的，為什么？id字段有什么特殊性呢？

通過表結構可以看出id字段是主鍵，查詢官方文檔，有針對主鍵列的解釋。

SELECT name, address, MAX(age) FROM t GROUP BY name;
The query is valid if name is a primary key of t or is a unique NOT NULL column. In such cases,MySQL recognizes that the selected column is functionally dependent on a grouping column. Forexample, if name is a primary key, its value determines the value of address because each group has only one value of the primary key and thus only one row. As a result, there is no randomness in the choice of address value in a group and no need to reject the query.

The query is invalid if name is not a primary key of t or a unique NOT NULL column.

大致的意思是：如果name列是主鍵或者是唯一的非空列，name上面的查詢是有效的。這種情況下，MySQL能夠識別出select中的列依賴于group by中的列。比如說，如果name是主鍵，它的值就決定了address的值，因為每個組只有一個主鍵值，分組中的每一行都具有唯一性，因此也不需要拒絕這個查詢。

4. 驗證唯一非空索引

增加name字段的唯一性約束

alter table student add unique(name);mysql> select id,name,score from student where score >95  group by name;+----+------+-------+| id | name | score |+----+------+-------+|  7 | Liam |   100 ||  3 | Lily |    99 ||  1 | Tom  |    96 |+----+------+-------+3 rows in set (0.00 sec)

通過上面的例子也驗證了，對于有唯一性約束的字段，也可以不用在group by中把select中的字段全部列出來。不過針對主鍵或者唯一性字段進行分組查詢意義并不是很大，因為他們的每一行都是唯一的。

ONLY_FULL_GROUP_BY

我們在上面提到select中的列都出現在group by中，其實在MySQL5.7.5之前是沒有此類限制的，5.7.5版本在sql_mode中增加了ONLY_FULL_GROUP_BY參數，用來開啟或者關閉針對group by的限制。下面我們在分別開啟和關閉ONLY_FULL_GROUP_BY限制的情況下分別進行驗證。

1. 我們先查詢下sql_mode

mysql> select @@sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+| @@sql_mode                                                                                                                                |+-------------------------------------------------------------------------------------------------------------------------------------------+| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+-------------------------------------------------------------------------------------------------------------------------------------------++1 row in set (0.00 sec)

2. sql_mode動態(tài)去除ONLY_FULL_GROUP_BY限制

mysql> SET @@sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');Query OK, 0 rows affected (0.05 sec)

再次執(zhí)行分組查詢

mysql> select id,name,score from student where score >95  group by score;+----+------+-------+| id | name | score |+----+------+-------+|  1 | Tom  |    96 ||  3 | Lily |    99 ||  7 | Liam |   100 |+----+------+-------+3 rows in set (0.00 sec)

3. sql_mode動態(tài)增加ONLY_FULL_GROUP_BY限制

SET @@sql_mode = sys.list_add(@@sql_mode, 'ONLY_FULL_GROUP_BY');

再次執(zhí)行分組查詢

mysql> select id,name,score from student where score >95  group by score;ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by。

分組查詢時，select的字段是否一定要都在group by中?

分組查詢時，select的字段是否一定要都在group by中?