/*数据如下：name
val memoa
2 a2(a的第二个值)a
1 a1--a的第一个值a
3 a3:a的第三个值b
1 b1--b的第一个值b
3 b3:b的第三个值b
2 b2b2b2b2b
4 b4b4b
5 b5b5b5b5b5*/--创建表并插入数据：create table tb(name varchar(10),val int,memo varchar(20))insert into tb values('a',
2, 'a2(a的第二个值)')insert into tb values('a',
1, 'a1--a的第一个值')insert into tb values('a',
3, 'a3:a的第三个值')insert into tb values('b',
1, 'b1--b的第一个值')insert into tb values('b',
3, 'b3:b的第三个值')insert into tb values('b',
2, 'b2b2b2b2')insert into tb values('b',
4, 'b4b4')insert into tb values('b',
5, 'b5b5b5b5b5')go --一、按name分组取val最大的值所在行的数据。--方法1：select a.* from tb
a where val
= (select max(val) from tb where name =
a.name) order by a.name--方法2：select a.* from tb
a where not exists(select 1 from tb where name =
a.name and val
> a.val)--方法3：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--方法4：select a.* from tb
a inner join (select name , max(val)
val from tb group by name)
b on a.name =
b.name and a.val
= b.val order by a.name--方法5select a.* from tb
a where 1
> (select count(*) from tb where name =
a.name and val
> a.val ) order by a.name/*name
val memo ----------
----------- -------------------- a
3 a3:a的第三个值b
5 b5b5b5b5b5*/ |
写法6
SELECT *
FROM (
SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY name ORDER BY val DESC ) rid
FROM tb
) AS t
WHERE rid = 1
如果上述存在一个name组中有两个以上相同的val，则查询方法1-5不正确。
例如：
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 3, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
