魏长东

weichangdong

mysql分组取每组前几条记录

    之前那在网上看到的,有种久渴逢甘露的感觉(这些mysql技巧,属于萧峰降龙十八掌的级别),就迫不及待的收录了,来源不知道是哪位高人了,要是有幸看见这个(貌似没有可能呵,我这边远蛮荒之地),请留言我加上出处。

--按某一字段分组取最大()值所在行的数据

/*
数据如下:
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    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');

 --一、按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
--方法5
select 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

*/ 

 推荐使用1,3,4,结果显示1,3,4效率相同,25效率差些,不过我3,4效率相同毫无疑问,1就不一样了,想不搞了。

--二、按name分组取val最小的值所在行的数据。

--方法1

select a.* from tb a where val = (select min(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,min(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 , min(val) val from tb group by name) b on

 a.name = b.name and a.val = b.val order by a.name
--方法5
select 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          1           a1--a的第一个值
b          1           b1--b的第一个值

*/ 


 --三、按name分组取第一次出现的行所在的数据。


select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          2           a2(a的第二个值)
b          1           b1--b的第一个值

*/  


 

--四、按name分组随机取一条数据。


select a.* from tb a where val = (select top 1 val from tb where name = a.name order by

 newid()) order by a.name

/*
name       val         memo                
---------- ----------- --------------------
a          1           a1--a的第一个值
b          5           b5b5b5b5b5

*/ 


 --五、按name分组取最小的两个(N)val


select a.* from tb a where 2 > (select count(*from tb where name = a.name and val < a.val ) 

order by a.name,a.val

select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val)

 order by a.name,a.val
select a.* from tb a where exists (select count(*from tb where name = a.name and val < a.val 

having Count(*< 2

order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2

*/ 


 --六、按name分组取最大的两个(N)val


select a.* from tb a where 2 > (select count(*from tb where name = a.name and val > a.val ) 

order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc

order by a.name,a.val
select a.* from tb a where exists (select count(*from tb where name = a.name and val > a.val 

having Count(*< 2order by a.name
/*
name       val         memo                
---------- ----------- --------------------
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5

*/  

 

--七,假如整行数据有重复,所有的列都相同(例如下表中的第5,6两行数据完全相同)。

        name分组取最大的两个(N)val


 1  /*
 2
 数据如下:
 3
 name val memo
 4
 a    2   a2(a的第二个值)
 5
 a    1   a1--a的第一个值
 6
 a    1   a1--a的第一个值
 7
 a    3   a3:a的第三个值
 8
 a    3   a3:a的第三个值
 9
 b    1   b1--b的第一个值
10
 b    3   b3:b的第三个值
11
 b    2   b2b2b2b2
12
 b    4   b4b4
13
 b    5   b5b5b5b5b5
14
 
15
 */ 
16