不知道可行: select t2.rq, t1.name, dense_rank() over(partition by t2.rq, t2.code order by name) as value from t1, t2 where t1.code = t2.code http://www.cnblogs.com/sunsonbaby/archive/2004/09/29/47876.aspx
select rq,name,value from t1 left join t2 on t1.code=t2.code order by rq,name
看看是不是你要的结果declare type recd is record ( rq varchar2(10), name varchar2(10), value varchar2(10) ); reder recd; type t_tab is table of reder%type INDEX BY BINARY_INTEGER; aa t_tab; cursor UB is select rq from t2 group by rq; j integer:=0; begin <<loop1>> for tab in UB loop <<loop2>> for i in 1..3 loop aa(i+j).rq:=tab.rq; if i=1 then aa(i+j).name:='n1'; elsif i=2 then aa(i+j).name:='n2'; else aa(i+j).name:='n3'; end if; select a.value into aa(i+j).value from (select * from t2 where t2.rq=tab.rq) a, t1 b where b.code=a.code(+) and b.name=aa(i+j).name; end loop loop2; j:=j+3; end loop loop1; for k in 1..aa.count loop dbms_output.put_line(aa(k).rq||' '||aa(k).name||' '||aa(k).value); end loop; end;
各位,不对啊。只能得到如下记录:rq name value ------------------ 01 n1 1 01 n3 2 02 n1 3 n2 02 n3 4 还有希望通过视图得到
SQL> select * from t1;C NAME - ---- a n1 b n2 c n3SQL> select * from t2;RQ C VALUE -- - ---------- 01 a 1 01 c 2 02 a 3 02 c 4SQL> create view t3 as 2 select a.rq,name,value 3 from (select distinct a.code,b.rq,a.name from t1 a,t2 b) a left outer join t2 b on a.code=b.code and a.rq=b.rq;视图已建立。SQL> select * from t3 order by 1;RQ NAME VALUE -- ---- ---------- 01 n1 1 01 n2 01 n3 2 02 n1 3 02 n2 02 n3 4已选择6行。
select c.rq,c.name,d.value (select a.rq,b.code,b.name from (select distinct '1' as id,rq from t2) a, (select '1' as id,code,name from t1) b where a.id = b.id (+)) c,t2 d where c.rq = d.rq (+) and c.code = d.code (+)
select
t2.rq,
t1.name,
dense_rank() over(partition by t2.rq, t2.code order by name) as value
from
t1, t2
where
t1.code = t2.code
http://www.cnblogs.com/sunsonbaby/archive/2004/09/29/47876.aspx
left join t2 on t1.code=t2.code
order by rq,name
type recd is record (
rq varchar2(10),
name varchar2(10),
value varchar2(10) );
reder recd;
type t_tab is table of reder%type INDEX BY BINARY_INTEGER;
aa t_tab;
cursor UB is select rq from t2 group by rq;
j integer:=0;
begin
<<loop1>>
for tab in UB loop
<<loop2>>
for i in 1..3 loop
aa(i+j).rq:=tab.rq;
if i=1 then
aa(i+j).name:='n1';
elsif i=2 then
aa(i+j).name:='n2';
else
aa(i+j).name:='n3';
end if;
select a.value into aa(i+j).value
from (select * from t2 where t2.rq=tab.rq) a, t1 b
where b.code=a.code(+) and b.name=aa(i+j).name;
end loop loop2;
j:=j+3;
end loop loop1;
for k in 1..aa.count loop
dbms_output.put_line(aa(k).rq||' '||aa(k).name||' '||aa(k).value);
end loop;
end;
------------------
01 n1 1
01 n3 2
02 n1 3
n2
02 n3 4
还有希望通过视图得到
- ----
a n1
b n2
c n3SQL> select * from t2;RQ C VALUE
-- - ----------
01 a 1
01 c 2
02 a 3
02 c 4SQL> create view t3 as
2 select a.rq,name,value
3 from (select distinct a.code,b.rq,a.name from t1 a,t2 b) a left outer join
t2 b on a.code=b.code and a.rq=b.rq;视图已建立。SQL> select * from t3 order by 1;RQ NAME VALUE
-- ---- ----------
01 n1 1
01 n2
01 n3 2
02 n1 3
02 n2
02 n3 4已选择6行。
(select a.rq,b.code,b.name from
(select distinct '1' as id,rq from t2) a,
(select '1' as id,code,name from t1) b
where a.id = b.id (+)) c,t2 d
where c.rq = d.rq (+) and c.code = d.code (+)