mysql练习

学习创建表的结构数据

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;