oracle数据库中存在如下表及数据
create table t_a(a number ,b varchar2(10),c varchar2(20));
insert into t_a values(1,'aa','aaa');
insert into t_a values(1,'bb','bbb');
insert into t_a values(2,'cc','ccc');
insert into t_a values(2,'dd','ddd');
insert into t_a values(8,'ee','eee');
insert into t_a values(8,'ff','fff');
insert into t_a values(8,'ff','hhh');
commit;
现欲获取如下结果
1 aa aaa
2 cc ccc
8 ee eee
请问各位pl/sql语句怎么写呢
create table t_a(a number ,b varchar2(10),c varchar2(20));
insert into t_a values(1,'aa','aaa');
insert into t_a values(1,'bb','bbb');
insert into t_a values(2,'cc','ccc');
insert into t_a values(2,'dd','ddd');
insert into t_a values(8,'ee','eee');
insert into t_a values(8,'ff','fff');
insert into t_a values(8,'ff','hhh');
commit;
现欲获取如下结果
1 aa aaa
2 cc ccc
8 ee eee
请问各位pl/sql语句怎么写呢
---SQLSERVER2005如下:
create table t_a(a int ,b varchar(10),c varchar(20));
goinsert into t_a values(1,'aa','aaa');
insert into t_a values(1,'bb','bbb');
insert into t_a values(2,'cc','ccc');
insert into t_a values(2,'dd','ddd');
insert into t_a values(8,'ee','eee');
insert into t_a values(8,'ff','fff');
insert into t_a values(8,'ff','hhh');with t as(
select row_number() over(partition by a order by a) nm,
*
from t_a
)
select a,b,c from t
where nm=1
where not exists
(select 1 from t_a
where t.a=a
and t.b>b)
insert into t_a values(3,'xa','cde');
insert into t_a values(3,'xa','rrr');
commit;
insert into t_a values(3,'xa','cde');
insert into t_a values(3,'xa','rrr');
commit;