慢慢赚钱博客

10月 17 2017

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

Written by admin