sql按分组求最大最小值

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

  2 —  www.moneyslow.com  

  3 /*

  4 数据如下:

  5 name val memo

  6 a    2   a2(a的第二个值)

  7 a    1   a1–a的第一个值

  8 a    3   a3:a的第三个值

  9 b    1   b1–b的第一个值

 10 b    3   b3:b的第三个值

 11 b    2   b2b2b2b2

 12 b    4   b4b4

 13 b    5   b5b5b5b5b5

 14 */

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

 39 name       val         memo                 

 40 ———- ———– ——————– 

 41 a          3           a3:a的第三个值

 42 b          5           b5b5b5b5b5

 43 */

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

 57 name       val         memo                 

 58 ———- ———– ——————– 

 59 a          1           a1–a的第一个值

 60 b          1           b1–b的第一个值

 61 */

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

 66 name       val         memo                 

 67 ———- ———– ——————– 

 68 a          2           a2(a的第二个值)

 69 b          1           b1–b的第一个值

 70 */

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

 75 name       val         memo                 

 76 ———- ———– ——————– 

 77 a          1           a1–a的第一个值

 78 b          5           b5b5b5b5b5

 79 */

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

 86 name       val         memo                 

 87 ———- ———– ——————– 

 88 a          1           a1–a的第一个值

 89 a          2           a2(a的第二个值)

 90 b          1           b1–b的第一个值

 91 b          2           b2b2b2b2

 92 */

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

 99 name       val         memo                 

100 ———- ———– ——————– 

101 a          2           a2(a的第二个值)

102 a          3           a3:a的第三个值

103 b          4           b4b4

104 b          5           b5b5b5b5b5

105 */

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

107 /*

108 数据如下:

109 name val memo

110 a    2   a2(a的第二个值)

111 a    1   a1–a的第一个值

112 a    1   a1–a的第一个值

113 a    3   a3:a的第三个值

114 a    3   a3:a的第三个值

115 b    1   b1–b的第一个值

116 b    3   b3:b的第三个值

117 b    2   b2b2b2b2

118 b    4   b4b4

119 b    5   b5b5b5b5b5

120 */

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

149 name       val         memo

150 ———- ———– ——————–

151 a          1           a1–a的第一个值

152 b          1           b1–b的第一个值

153 

154 (2 行受影响)

155 */

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

182 name       val         memo

183 ———- ———– ——————–

184 a          1           a1–a的第一个值

185 b          1           b1–b的第一个值

186 

187 (2 行受影响)

188 */

京ICP备11047313号-21