分组取最大

分组取最大

Sql代码

1. --按某一字段分组取最大(小)值所在行的数据(2007-10-23于浙江杭州)   
2. /*   
3. 数据如下：   
4. name val memo   
5. a    2   a2(a的第二个值)   
6. a    1   a1--a的第一个值   
7. a    3   a3:a的第三个值   
8. b    1   b1--b的第一个值   
9. b    3   b3:b的第三个值   
10. b    2   b2b2b2b2   
11. b    4   b4b4   
12. b    5   b5b5b5b5b5   
13. */   
14. --创建表并插入数据：   
15. create table tb(name varchar(10),val int,memo varchar(20))   
16. insert into tb values('a',    2,   'a2(a的第二个值)')   
17. insert into tb values('a',    1,   'a1--a的第一个值')   
18. insert into tb values('a',    3,   'a3:a的第三个值')   
19. insert into tb values('b',    1,   'b1--b的第一个值')   
20. insert into tb values('b',    3,   'b3:b的第三个值')   
21. insert into tb values('b',    2,   'b2b2b2b2')   
22. insert into tb values('b',    4,   'b4b4')   
23. insert into tb values('b',    5,   'b5b5b5b5b5')   
24. go   
25.   
26. --一、按name分组取val最大的值所在行的数据。   
27. --方法1：   
28. select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name  
29. --方法2：   
30. select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)   
31. --方法3：   
32. 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  
33. --方法4：   
34. 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  
35. --方法5   
36. select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name  
37. /*   
38. name       val         memo                    
39. ---------- ----------- --------------------    
40. a          3           a3:a的第三个值   
41. b          5           b5b5b5b5b5   
42. */   
43.   
44. --二、按name分组取val最小的值所在行的数据。   
45. --方法1：   
46. select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name  
47. --方法2：   
48. select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)   
49. --方法3：   
50. 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  
51. --方法4：   
52. 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  
53. --方法5   
54. select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name  
55. /*   
56. name       val         memo                    
57. ---------- ----------- --------------------    
58. a          1           a1--a的第一个值   
59. b          1           b1--b的第一个值   
60. */   
61.   
62. --三、按name分组取第一次出现的行所在的数据。   
63. select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name  
64. /*   
65. name       val         memo                    
66. ---------- ----------- --------------------    
67. a          2           a2(a的第二个值)   
68. b          1           b1--b的第一个值   
69. */   
70.   
71. --四、按name分组随机取一条数据。   
72. select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name  
73. /*   
74. name       val         memo                    
75. ---------- ----------- --------------------    
76. a          1           a1--a的第一个值   
77. b          5           b5b5b5b5b5   
78. */   
79.   
80. --五、按name分组取最小的两个(N个)val   
81. 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   
82. 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   
83. 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  
84. /*   
85. name       val         memo                    
86. ---------- ----------- --------------------    
87. a          1           a1--a的第一个值   
88. a          2           a2(a的第二个值)   
89. b          1           b1--b的第一个值   
90. b          2           b2b2b2b2   
91. */   
92.   
93. --六、按name分组取最大的两个(N个)val   
94. 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   
95. 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   
96. 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  
97. /*   
98. name       val         memo                    
99. ---------- ----------- --------------------    
100. a          2           a2(a的第二个值)   
101. a          3           a3:a的第三个值   
102. b          4           b4b4   
103. b          5           b5b5b5b5b5   
104. */