数据:
列A 列B
a 206
c 211
b 211
b 226
d 234
a 236
a 251
b 263
要求查询出来“列A”中最大值,而且此值不能与其他”列A“中的值相同,最终查询出来的结果应该是:
d 234
a 251
b 263
列A 列B
a 206
c 211
b 211
b 226
d 234
a 236
a 251
b 263
要求查询出来“列A”中最大值,而且此值不能与其他”列A“中的值相同,最终查询出来的结果应该是:
d 234
a 251
b 263
select A,max(B)
from tablename
group by A
on a.列B=b.列B group by a.列A
我用这样的语句得出的结果好像是对的,高人帮忙看看有没有问题
from tablea a
where not exists (select 1
from tablea b
where a.a = b.a
and a.b = b.b
and a.rowid != b.rowid)
group by a.a;
FROM(
select A,B
from tablename
group by A,B
having count(*)<2
GROUP BY A
with tb as
(
select 'a' a,206 b from dual union all
select 'c', 211 from dual union all
select 'b', 211 from dual union all
select 'b', 226 from dual union all
select 'd', 234 from dual union all
select 'a' ,236 from dual union all
select 'a' ,251 from dual union all
select 'b', 263 from dual)
select a.a,max(a.b)
from tb a
where (select count(*) from tb b where a.b=b.b)<=1
group by a.a
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL>
SQL> with tb as
2 (
3 select 'a' a,206 b from dual union all
4 select 'c', 211 from dual union all
5 select 'b', 211 from dual union all
6 select 'b', 226 from dual union all
7 select 'd', 234 from dual union all
8 select 'a' ,236 from dual union all
9 select 'a' ,251 from dual union all
10 select 'b', 263 from dual)
11 select a.a,max(a.b)
12 from tb a
13 where (select count(*) from tb b where a.b=b.b)<=1
14 group by a.a
15 /
A MAX(A.B)
- ----------
d 234
a 251
b 263