现在有一个表table_a 有三个字段a,b,c值为
a b c
1 1 1
3 1 2
1 1 4
4 2 5
想要的检索结果是
a b c
1 1 1
3 1 2
4 2 5
也就是想要把(a b)重复的数据只取一条,但同时也要取得对应的c.
不知道有没有什么好的办法??????
a b c
1 1 1
3 1 2
1 1 4
4 2 5
想要的检索结果是
a b c
1 1 1
3 1 2
4 2 5
也就是想要把(a b)重复的数据只取一条,但同时也要取得对应的c.
不知道有没有什么好的办法??????
(1)
select x.a, x.b, x.c
from table_a x
where c = (select c from table_a y
where x.a = y.a
and x.b = y.b
and rownum = 1)(2)
select a.a,a.b,a.c from table_a a
where a.rowid not in (Select max(b.rowid) from table_a b)回去你慢慢研究
where not exists(Select * from table_aa
Where a=t.a and b=t.b and c<t.c)
where not exists(Select * from table_a
Where a=t.a and b=t.b and c<t.c)------ 这条语句没有错,意思是取重复列中 c列最小的那列。
(
select *,row_number() over (partition by a,b order by c) rn from table_a
) t
where t.rn=1
from table_a
group by a, b這個應該也可以.反正方法就是有N多種.多學習~~
create table table_a(
a number,
b number,
c number
);insert into table_a
values (1, 1, 1);
insert into table_a
values (3, 1, 2);
insert into table_a
values (1, 1, 4);
insert into table_a
values (4, 2, 5);
commit ;-----------------
select f.a, f.b, f.c
from (select ta.*,
row_number () over (partition by ta.a, ta.b order by ta.c) rn
from table_a ta) f
where f.rn = 1
a b c
1 1 6
3 1 2
1 1 4
4 2 5
3 1 3
想要的结果是:
a b c
1 1 6
3 1 2
4 2 5
就不好办了。不知道还有没有更好的方法。再等2个小时,就结贴!