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

详细版本见个人博客：MySQL中的GROUP BY获取其他字段方法

---

创建测试数据

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

create table show_plan(
id int primary key auto_increment,
name varchar(255),
desp int,
price int
);

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

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);

+----+------+------+-------+
| 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 |
+----+------+------+-------+

问题

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

分析与解答

一个典型的错误做法

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

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

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

MySQL也会报出如下错误：

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

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;

+------+------+-------+
| 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

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
);

+------+------+-------+
| name | desp | price |
+------+------+-------+
| a    |    2 |    90 |
| a    |    3 |   100 |
| d    |    1 |    77 |
| e    |    4 |    75 |
+------+------+-------+

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

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

---

详细版本见个人博客：MySQL中的GROUP BY获取其他字段方法