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