sql 分组筛选 很强大

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

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

 

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

  2 --   www.2cto.com  

  3

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

  16 create table tb(name varchar(10),val int,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 = (select max(val) from tb where name = a.name) order by a.name

  30 --方法2：

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

  32 --方法3：

  33 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

  34 --方法4：

  35 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

  36 --方法5

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

  38

  44 

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

  46 --方法1：

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

  48 --方法2：

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

  50 --方法3：

  51 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

  52 --方法4：

  53 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

  54 --方法5

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

  56

  62 

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

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

  65

  71 

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

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

  74

  80 

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

  82 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

  83 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

  84 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

  85

  93 

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

  95 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

  96 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

  97 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

  98

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

107

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

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

123 create table tb(name varchar(10),val int,memo varchar(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,memo varchar(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(order by name , val) from tb

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

175 (

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

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

178 

179 drop table tb

180 

181