sql 分组筛选 很强大

首先声明，转来的，尊重原创：http://www.2cto.com/database/201211/169178.html

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

 

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

  2 --  www.2cto.com 

  3

 15 --创建表并插入数据：

 16 create table tb(name varchar(10),valint,memo varchar(20))

 17 insert into tb values('a',   2,  'a2(a的第二个值)')

 18 insert into tb values('a',   1,  'a1--a的第一个值')

 19 insert into tb values('a',   3,  'a3:a的第三个值')

 20 insert into tb values('b',   1,  'b1--b的第一个值')

 21 insert into tb values('b',   3,  'b3:b的第三个值')

 22 insert into tb values('b',   2,  'b2b2b2b2')

 23 insert into tb values('b',   4,  'b4b4')

 24 insert into tb values('b',   5,  'b5b5b5b5b5')

 25 go

 26 

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

 28 --方法1：

 29 select a.* from tb a where val = (selectmax(val) from tb where name = a.name) order by a.name

 30 --方法2：

 31 select a.* from tb a where notexists(select 1 from tb where name = a.name and val >a.val)

 32 --方法3：

 33 select a.* from tb a,(selectname,max(val) val from tb group by name) b where a.name = b.nameand a.val = b.val order by a.name

 34 --方法4：

 35 select a.* from tb a inner join (selectname , max(val) val from tb group by name) b on a.name = b.name anda.val = b.val order by a.name

 36 --方法5

 37 select a.* from tb a where 1 > (selectcount(*) from tb where name = a.name and val > a.val ) order bya.name

 38

 44 

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

 46 --方法1：

 47 select a.* from tb a where val = (selectmin(val) from tb where name = a.name) order by a.name

 48 --方法2：

 49 select a.* from tb a where notexists(select 1 from tb where name = a.name and val <a.val)

 50 --方法3：

 51 select a.* from tb a,(selectname,min(val) val from tb group by name) b where a.name = b.nameand a.val = b.val order by a.name

 52 --方法4：

 53 select a.* from tb a inner join (selectname , min(val) val from tb group by name) b on a.name = b.name anda.val = b.val order by a.name

 54 --方法5

 55 select a.* from tb a where 1 > (selectcount(*) from tb where name = a.name and val < a.val) order bya.name

 56

 62 

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

 64 select a.* from tb a where val = (selecttop 1 val from tb where name = a.name) order by a.name

 65

 71 

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

 73 select a.* from tb a where val = (selecttop 1 val from tb where name = a.name order by newid()) order bya.name

 74

 80 

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

 82 select a.* from tb a where 2 > (selectcount(*) from tb where name = a.name and val < a.val ) order bya.name,a.val

 83 select a.* from tb a where val in (selecttop 2 val from tb where name=a.name order by val) order bya.name,a.val

 84 select a.* from tb a where exists (selectcount(*) from tb where name = a.name and val < a.val havingCount(*) < 2) order by a.name

 85

 93 

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

 95 select a.* from tb a where 2 > (selectcount(*) from tb where name = a.name and val > a.val ) order bya.name,a.val

 96 select a.* from tb a where val in (selecttop 2 val from tb where name=a.name order by val desc) order bya.name,a.val

 97 select a.* from tb a where exists (selectcount(*) from tb where name = a.name and val > a.val havingCount(*) < 2) order by a.name

 98

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

107

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

122 --创建表并插入数据：

123 create table tb(name varchar(10),val int,memovarchar(20))

124 insert into tb values('a',   2,   'a2(a的第二个值)')

125 insert into tb values('a',   1,   'a1--a的第一个值')

126 insert into tb values('a',   1,   'a1--a的第一个值')

127 insert into tb values('a',   3,   'a3:a的第三个值')

128 insert into tb values('a',   3,   'a3:a的第三个值')

129 insert into tb values('b',   1,   'b1--b的第一个值')

130 insert into tb values('b',   3,   'b3:b的第三个值')

131 insert into tb values('b',   2,   'b2b2b2b2')

132 insert into tb values('b',   4,   'b4b4')

133 insert into tb values('b',   5,   'b5b5b5b5b5')

134 go

135 

136 select * , px = identity(int,1,1) into tmp from tb

137 

138 select m.name,m.val,m.memo from

139 (

140   select t.* from tmp t where val =(select min(val) from tmp where name = t.name)

141 ) m where px = (select min(px) from

142 (

143   select t.* from tmp t where val =(select min(val) from tmp where name = t.name)

144 ) n where n.name = m.name)

145 

146 drop table tb,tmp

147 

148

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

157 --创建表并插入数据：

158 create table tb(name varchar(10),val int,memovarchar(20))

159 insert into tb values('a',   2,   'a2(a的第二个值)')

160 insert into tb values('a',   1,   'a1--a的第一个值')

161 insert into tb values('a',   1,   'a1--a的第一个值')

162 insert into tb values('a',   3,   'a3:a的第三个值')

163 insert into tb values('a',   3,   'a3:a的第三个值')

164 insert into tb values('b',   1,   'b1--b的第一个值')

165 insert into tb values('b',   3,   'b3:b的第三个值')

166 insert into tb values('b',   2,   'b2b2b2b2')

167 insert into tb values('b',   4,   'b4b4')

168 insert into tb values('b',   5,   'b5b5b5b5b5')

169 go

170 

171 select m.name,m.val,m.memo from

172 (

173   select * , px = row_number() over(orderby name , val) from tb

174 ) m where px = (select min(px) from

175 (

176   select * , px = row_number() over(orderby name , val) from tb

177 ) n where n.name = m.name)

178 

179 drop table tb

180 

181