SQL按某一字段分组取最大(小)值所在行的数据(二)
e 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的第三个值')
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