SQL按某一字段分组取最大(小)值所在行的数据
1 --按某一字段分组取最大(小)值所在行的数据
2 -- www.2cto.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最小的值所在行的数据。
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 nam