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