首先我们创建一个表tb然后插入数据
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values(‘a’, 2, ‘a2’)
insert into tb values(‘a’, 1, ‘a1’)
insert into tb values(‘a’, 3, ‘a3’)
insert into tb values(‘b’, 1, ‘b1’)
insert into tb values(‘b’, 3, ‘b3’)
insert into tb values(‘b’, 2, ‘b2’)
insert into tb values(‘b’, 4, ‘b4’)
insert into tb values(‘b’, 5, ‘b5’)
mysql数据库
A.按name分组查询val最大值和最小值
1.取最大值
第一种方法
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
第二种方法
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
第三种方法
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
第四种方法
select a.* from tb a inner join (select name , max(val) val from tb group by