【SQL精彩语句】按某一字段分组取最大(小)值所在行的数据

  1 --按某一字段分组取最大(小)值所在行的数据
  2 --(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
  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 */