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*/

--创建表并插入数据：

createtabletb(namevarchar(10),valint,memovarchar(20))insertintotbvalues('a',2,'a2(a的第二个值)')insertintotbvalues('a',1,'a1--a的第一个值')insertintotbvalues('a',3,'a3:a的第三个值')insertintotbvalues('b',1,'b1--b的第一个值')insertintotbvalues('b',3,'b3:b的第三个值')insertintotbvalues('b',2,'b2b2b2b2')insertintotbvalues('b',4,'b4b4')insertintotbvalues('b',5,'b5b5b5b5b5')go

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

--方法1：selecta.*fromtb awhereval=(selectmax(val)fromtbwherename=a.name)orderbya.name--方法2：selecta.*fromtb awherenotexists(select1fromtbwherename=a.nameandval>a.val)--方法3：selecta.*fromtb a,(selectname,max(val) valfromtbgroupbyname) bwherea.name=b.nameanda.val=b.valorderbya.name--方法4：selecta.*fromtb ainnerjoin(selectname ,max(val) valfromtbgroupbyname) bona.name=b.nameanda.val=b.valorderbya.name--方法5selecta.*fromtb awhere1>(selectcount(*)fromtbwherename=a.nameandval>a.val )orderbya.name/*name val memo

---------- ----------- --------------------

a 3 a3:a的第三个值

b 5 b5b5b5b5b5*/

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

--方法1：selecta.*fromtb awhereval=(selectmin(val)fromtbwherename=a.name)orderbya.name--方法2：selecta.*fromtb awherenotexists(select1fromtbwherename=a.nameandval(selectcount(*)fromtbwherename=a.nameandval

---------- ----------- --------------------

a 1 a1--a的第一个值

b 1 b1--b的第一个值*/

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

selecta.*fromtb awhereval=(selecttop1valfromtbwherename=a.name)orderbya.name/*name val memo

---------- ----------- --------------------

a 2 a2(a的第二个值)

b 1 b1--b的第一个值*/

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

selecta.*fromtb awhereval=(selecttop1valfromtbwherename=a.nameorderbynewid())orderbya.name/*name val memo

---------- ----------- --------------------

a 1 a1--a的第一个值

b 5 b5b5b5b5b5*/

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

selecta.*fromtb awhere2>(selectcount(*)fromtbwherename=a.nameandval

---------- ----------- --------------------

a 1 a1--a的第一个值

a 2 a2(a的第二个值)

b 1 b1--b的第一个值

b 2 b2b2b2b2*/

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

selecta.*fromtb awhere2>(selectcount(*)fromtbwherename=a.nameandval>a.val )orderbya.name,a.valselecta.*fromtb awherevalin(selecttop2valfromtbwherename=a.nameorderbyvaldesc)orderbya.name,a.valselecta.*fromtb awhereexists(selectcount(*)fromtbwherename=a.nameandval>a.valhavingCount(*)<2)orderbya.name/*name val memo

---------- ----------- --------------------

a 2 a2(a的第二个值)

a 3 a3:a的第三个值

b 4 b4b4

b 5 b5b5b5b5b5*/

--七，假如整行数据有重复，所有的列都相同。

/*数据如下：

name val memo

a 2 a2(a的第二个值)

a 1 a1--a的第一个值

a 1 a1--a的第一个值

a 3 a3:a的第三个值

a 3 a3:a的第三个值

b 1 b1--b的第一个值

b 3 b3:b的第三个值

b 2 b2b2b2b2

b 4 b4b4

b 5 b5b5b5b5b5*/

--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。

--创建表并插入数据：

createtabletb(namevarchar(10),valint,memovarchar(20))insertintotbvalues('a',2,'a2(a的第二个值)')insertintotbvalues('a',1,'a1--a的第一个值')insertintotbvalues('a',1,'a1--a的第一个值')insertintotbvalues('a',3,'a3:a的第三个值')insertintotbvalues('a',3,'a3:a的第三个值')insertintotbvalues('b',1,'b1--b的第一个值')insertintotbvalues('b',3,'b3:b的第三个值')insertintotbvalues('b',2,'b2b2b2b2')insertintotbvalues('b',4,'b4b4')insertintotbvalues('b',5,'b5b5b5b5b5')goselect*, px=identity(int,1,1)intotmpfromtbselectm.name,m.val,m.memofrom(selectt.*fromtmp twhereval=(selectmin(val)fromtmpwherename=t.name)

) mwherepx=(selectmin(px)from(selectt.*fromtmp twhereval=(selectmin(val)fromtmpwherename=t.name)

) nwheren.name=m.name)droptabletb,tmp/*name val memo

---------- ----------- --------------------

a 1 a1--a的第一个值

b 1 b1--b的第一个值

(2 行受影响)*/

--在sql server 2005中可以使用row_number函数，不需要使用临时表。

--创建表并插入数据：

createtabletb(namevarchar(10),valint,memovarchar(20))insertintotbvalues('a',2,'a2(a的第二个值)')insertintotbvalues('a',1,'a1--a的第一个值')insertintotbvalues('a',1,'a1--a的第一个值')insertintotbvalues('a',3,'a3:a的第三个值')insertintotbvalues('a',3,'a3:a的第三个值')insertintotbvalues('b',1,'b1--b的第一个值')insertintotbvalues('b',3,'b3:b的第三个值')insertintotbvalues('b',2,'b2b2b2b2')insertintotbvalues('b',4,'b4b4')insertintotbvalues('b',5,'b5b5b5b5b5')goselectm.name,m.val,m.memofrom(select*, px=row_number()over(orderbyname , val)fromtb

) mwherepx=(selectmin(px)from(select*, px=row_number()over(orderbyname , val)fromtb

) nwheren.name=m.name)droptabletb/*name val memo

---------- ----------- --------------------

a 1 a1--a的第一个值

b 1 b1--b的第一个值

(2 行受影响)*/