--按某一字段分组取最大(小)值所在行的数据
/*数据如下
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最大的值所在行的数据。
--方法1selecta.*fromtb awhereval(selectmax(val)fromtbwherenamea.name)orderbya.name--方法2selecta.*fromtb awherenotexists(select1fromtbwherenamea.nameandval>a.val)--方法3selecta.*fromtb a,(selectname,max(val) valfromtbgroupbyname) bwherea.nameb.nameanda.valb.valorderbya.name--方法4selecta.*fromtb ainnerjoin(selectname ,max(val) valfromtbgroupbyname) bona.nameb.nameanda.valb.valorderbya.name--方法5selecta.*fromtb awhere1>(selectcount(*)fromtbwherenamea.nameandval>a.val )orderbya.name/*name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5*/
--二、按name分组取val最小的值所在行的数据。
--方法1selecta.*fromtb awhereval(selectmin(val)fromtbwherenamea.name)orderbya.name--方法2selecta.*fromtb awherenotexists(select1fromtbwherenamea.nameandval(selectcount(*)fromtbwherenamea.nameandval
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值*/
--三、按name分组取第一次出现的行所在的数据。
selecta.*fromtb awhereval(selecttop1valfromtbwherenamea.name)orderbya.name/*name val memo
---------- ----------- --------------------
a 2 a2(a的第二个值)
b 1 b1--b的第一个值*/
--四、按name分组随机取一条数据。
selecta.*fromtb awhereval(selecttop1valfromtbwherenamea.nameorderbynewid())orderbya.name/*name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 5 b5b5b5b5b5*/
--五、按name分组取最小的两个(N个)val
selecta.*fromtb awhere2>(selectcount(*)fromtbwherenamea.nameandval
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2*/
--六、按name分组取最大的两个(N个)val
selecta.*fromtb awhere2>(selectcount(*)fromtbwherenamea.nameandval>a.val )orderbya.name,a.valselecta.*fromtb awherevalin(selecttop2valfromtbwherenamea.nameorderbyvaldesc)orderbya.name,a.valselecta.*fromtb awhereexists(selectcount(*)fromtbwherenamea.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*, pxidentity(int,1,1)intotmpfromtbselectm.name,m.val,m.memofrom(selectt.*fromtmp twhereval(selectmin(val)fromtmpwherenamet.name)
) mwherepx(selectmin(px)from(selectt.*fromtmp twhereval(selectmin(val)fromtmpwherenamet.name)
) nwheren.namem.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*, pxrow_number()over(orderbyname , val)fromtb
) mwherepx(selectmin(px)from(select*, pxrow_number()over(orderbyname , val)fromtb
) nwheren.namem.name)droptabletb/*name val memo
---------- ----------- --------------------
a 1 a1--a的第一个值
b 1 b1--b的第一个值
(2 行受影响)*/