MySQL中的GROUP BY获取其他字段方法

除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出。但是有时候,我们又需要在GROUP BY后选出其他列,本文给出了几个方法。

创建测试数据

CREATE TABLE语句用于创建表,在test数据库下面创建一张名为show_plan的表:

1
2
3
4
5
6
create table show_plan(
id int primary key auto_increment,
name varchar(255),
desp int,
price int
);

INSERT INTO 语句用于向表格中插入新的行,现在我们向show_plan表中插入一些测试数据:

1
2
3
4
5
6
7
8
9
10
11
12
insert into show_plan(name,desp,price)
values ('a',1,55),
('b',1,20),
('c',1,63),
('b',2,89),
('c',2,78),
('a',2,90),
('b',3,88),
('c',3,77),
('a',3,100),
('d',1,77),
('e',4,75);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
+----+------+------+-------+
| id | name | desp | price |
+----+------+------+-------+
| 1 | a | 1 | 55 |
| 2 | b | 1 | 20 |
| 3 | c | 1 | 63 |
| 4 | b | 2 | 89 |
| 5 | c | 2 | 78 |
| 6 | a | 2 | 90 |
| 7 | b | 3 | 88 |
| 8 | c | 3 | 77 |
| 9 | a | 3 | 100 |
| 10 | d | 1 | 77 |
| 11 | e | 4 | 75 |
+----+------+------+-------+

问题

查询出每个演出单位票房最高的剧目名称。

分析与解答

一个典型的错误做法

1
2
3
select name,desp,MAX(price)
from show_plan
group by desp;

注意:除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出

这里的select的name列并不在group by子句中。

MySQL也会报出如下错误:

1
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shows.show_plan.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

正确做法1

1
2
3
4
5
6
7
select a.name,a.desp,b.price
from show_plan as a,(
select desp,MAX(price) as price
from show_plan
group by desp
) as b
where a.price = b.price AND a.desp = b.desp;
1
2
3
4
5
6
7
8
+------+------+-------+
| name | desp | price |
+------+------+-------+
| a | 2 | 90 |
| a | 3 | 100 |
| d | 1 | 77 |
| e | 4 | 75 |
+------+------+-------+

如果只用price去匹配,假如存在有多个price相同时,

例如,desp=1的MAX(price)=77,但是desp=2中也含有price=77的行,那么就会被错误的选择出来。

所以要加一个AND a.desp = b.desp筛选条件。

正确做法2

1
2
3
4
5
6
7
8
select a.name,a.desp,a.price
from show_plan as a
where a.price in (
select MAX(b.price)
from show_plan as b
where b.desp = a.desp
group by b.desp
);
1
2
3
4
5
6
7
8
+------+------+-------+
| name | desp | price |
+------+------+-------+
| a | 2 | 90 |
| a | 3 | 100 |
| d | 1 | 77 |
| e | 4 | 75 |
+------+------+-------+

同样的,类似方法1,这里最关键的是where b.desp = a.desp这个筛选条件。

参考:关于group by 和max函数一起使用的坑

赞赏一杯咖啡
0%