mysql group by的用法
学习创建表的结构数据
create table t_person
(
article int,
author varchar(20),
price decimal(8, 2)
);
## 导入表的数据结构
insert into t_person
select 001, 'B', 3.99
union all
select 002, 'A', 10.99
union all
select 003, 'C', 1.99
union all
select 004, 'B', 19.95
union all
select 005, 'A', 6.96;
选出每个author的price最高的记录
# 选择出每个作者最高的记录
select tp.author, tp.price, tp.article
from t_person tp
where tp.price = (select max(p.price)
from t_person p
where tp.author = p.author);
##
select tp.author, tp.price, tp.article
from t_person tp,
(select author, max(price) as price from t_person group by author) as t
where tp.author = t.author
and tp.price = t.price;
## 方法二
select tp.author, tp.price, tp.article
from t_person tp inner join
(select author, max(price) as price from t_person group by author) as t
on tp.author = t.author
and tp.price = t.price;
##方法三
select t1.* from t_person t1 left join t_person t2
on t1.author = t2.author and t1.price < t2.price
where t2.author is null;