/*数据如下:
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个)valselecta.*fromtb awhere2>(selectcount(*)fromtbwherename=a.nameandval
---------- ----------- --------------------
a 1 a1--a的第一个值
a 2 a2(a的第二个值)
b 1 b1--b的第一个值
b 2 b2b2b2b2*/--六、按name分组取最大的两个(N个)valselecta.*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 行受影响)*/