第一个表(Person):
------------------------------------------------
ID FirstName LastName
------------------------------------------------
1 David Backham
2 ED Jason
3 Nan Xiao
4 Liu Endy
5 Wang Joe
6 Chang Lucy
------------------------------------------------第二张表(Country):
--------------------------------------------------------
国家 name1 name2 name3 name4 name5
--------------------------------------------------------
巴西 1 2 null null null
武汉 3 4 5 6 null
--------------------------------------------------------问题:要求写一个SQL,能够得出下面的结果:
--------------------------------------------------------
国家 名称
--------------------------------------------------------
巴西 David Backham|ED Jason
武汉 Nan Xiao|Liu Endy|Wang Joe|Chang Lucy
--------------------------------------------------------希望高手(大牛人)能够帮我,谢谢!!!
------------------------------------------------
ID FirstName LastName
------------------------------------------------
1 David Backham
2 ED Jason
3 Nan Xiao
4 Liu Endy
5 Wang Joe
6 Chang Lucy
------------------------------------------------第二张表(Country):
--------------------------------------------------------
国家 name1 name2 name3 name4 name5
--------------------------------------------------------
巴西 1 2 null null null
武汉 3 4 5 6 null
--------------------------------------------------------问题:要求写一个SQL,能够得出下面的结果:
--------------------------------------------------------
国家 名称
--------------------------------------------------------
巴西 David Backham|ED Jason
武汉 Nan Xiao|Liu Endy|Wang Joe|Chang Lucy
--------------------------------------------------------希望高手(大牛人)能够帮我,谢谢!!!
select b1.*,
(
select substr(max(sys_connect_by_path(names,'|')),2) result from
(select id,coun,names,rn,lead(rn) over(partition by coun order by rn) rn1
from (select id,coun,names,row_number() over(order by coun,id desc) rn from
(select b.id,coun,b.firstname||' '||b.lastname names from
(select coun,name1 t from country
union all
select coun,name2 t from country
union all
select coun,name3 t from country
union all
select coun,name4 t from country
union all
select coun,name5 t from country) a,person b where a.t=b.id) a1
)
)
start with coun = b1.coun and rn1 is null connect by rn1 = prior rn
) mingcheng
from (select distinct coun from
(select b.id,coun,b.firstname||' '||b.lastname names from
(select coun,name1 t from country
union
select coun,name2 t from country
union
select coun,name3 t from country
union
select coun,name4 t from country
union
select coun,name5 t from country) a,person b where a.t=b.id) a1) b1注:coun为国家,其他字段都一样大体思路:
1、SQL> (select coun,b.firstname||' '||b.lastname names from
2 (select coun,name1 t from country
3 union
4 select coun,name2 t from country
5 union
6 select coun,name3 t from country
7 union
8 select coun,name4 t from country
9 union
10 select coun,name5 t from country) a,persion b where a.t=b.id)
11 /COUN NAMES
-------------------- -----------------------------------------
Bazil David Backham
Bazil ED Jason
wuhan NAN Xiao
wuhan Liu Endy
wuhan Wang Joe
wuhan Chang Lucy6 rows selectedSQL>
2、再根据国家处理成lz要的结果
SQL> select b1.*,
2 (
3 select substr(max(sys_connect_by_path(names,'|')),2) result from
4 (select id,coun,names,rn,lead(rn) over(partition by coun order by rn) rn1
5 from (select id,coun,names,row_number() over(order by coun,id desc) rn from
6 (select b.id,coun,b.firstname||' '||b.lastname names from
7 (select coun,name1 t from country
8 union all
9 select coun,name2 t from country
10 union all
11 select coun,name3 t from country
12 union all
13 select coun,name4 t from country
14 union all
15 select coun,name5 t from country) a,persion b where a.t=b.id) a1
16 )
17 )
18 start with coun = b1.coun and rn1 is null connect by rn1 = prior rn
19 ) mingcheng
20 from (select distinct coun from
21 (select b.id,coun,b.firstname||' '||b.lastname names from
22 (select coun,name1 t from country
23 union
24 select coun,name2 t from country
25 union
26 select coun,name3 t from country
27 union
28 select coun,name4 t from country
29 union
30 select coun,name5 t from country) a,persion b where a.t=b.id) a1) b1
31 /COUN MINGCHENG
-------------------- --------------------------------------------------------------------------------
Bazil David Backham|ED Jason
wuhan NAN Xiao|Liu Endy|Wang Joe|Chang Lucy应该还可以优化,时间有限,:)
from Person b;
select 国家, (select firstname || lastname from person,Country c where id=name1 and b.国家=c.国家) || (select firstname || lastname from person,Country c where id=name2 and b.国家=c.国家) || (select firstname || lastname from person,Country c where id=name3 and b.国家=c.国家) ||(select firstname || lastname from person,Country c where id=name4 and b.国家=c.国家) ||(select firstname || lastname from person,Country c where id=name5 and b.国家=c.国家) as 名称 from Country b
||'|'|| p2.firstname||' '||p2.lastname
||'|'|| p3.firstname||' '||p3.lastname
||'|'|| p4.firstname||' '||p4.lastname
||'|'|| p5.firstname||' '||p5.lastname
from Country c
left outer join Person p1 On c.name1=p1.id
left outer join Person p2 On c.name2=p2.id
left outer join Person p3 On c.name3=p3.id
left outer join Person p4 On c.name4=p4.id
left outer join Person p5 On c.name5=p5.id
name2 number ,
name3 number ,
name4 number ,
name5 Number );Insert Into A Values (1,'David','Backham');
Insert Into A Values (2,'ED','Jason');
Insert Into A Values (3,'Nan','Xiao');
Insert Into A Values (4,'Liu','Endy');
Insert Into A Values (5,'Wang','Joe');
Insert Into A Values (6,'Chang','Lucy');Insert Into B Values ('baxi',1,2,Null,Null,Null);
Insert Into B Values ('wuhan',3,4,5,6,Null);Commit ;Select C ,Max (decode(seq,1,Name || '||',null)) || max(decode(seq,2,Name || '||' ,null)) ||
Max(decode(seq,3,Name || '||' ,null)) || max(decode(seq,4,Name ,null)) Name From (Select C , Name ,row_number() over( Partition By C Order By C) seq From ( Select C , A.FIRSTNAME || a.lastname Name , temp.n From
( Select C , name1 n From B
Union All
Select C , name2 n From B
Union All
Select C , name3 n From B
Union All
Select C , name4 n From B
Union All
Select C , name5 n From B
Order By C , n
) temp , A
Where A.Id = temp.n
)
)
Group By C 结果
1 baxi DavidBackham||EDJason
2 wuhan NanXiao||LiuEndy||WangJoe||ChangLucy
select a.国家, b1.FirstName||' '||b1.lastname
||'|'|| b2.FirstName||' '||b2.lastname
||'|'|| b3.FirstName||' '||b3.lastname
||'|'|| b4.FirstName||' '||b4.lastname
||'|'|| b5.FirstName||' '||b5.lastname
from Country a,Person b1,Person b2,Person b3,Person b4,Person 5
where
(a.name1=b1.id(+) and a.name1 is not null) and
(a.name2=b2.id(+) and a.name2 is not null) and
(a.name3=b3.id(+) and a.name3 is not null) and
(a.name4=b4.id(+) and a.name4 is not null) and
(a.name5=b5.id(+) and a.name5 is not null)
select a.国家 as 国家, b1.FirstName||' '||b1.lastname
||'|'|| b2.FirstName||' '||b2.lastname
||'|'|| b3.FirstName||' '||b3.lastname
||'|'|| b4.FirstName||' '||b4.lastname
||'|'|| b5.FirstName||' '||b5.lastname as 名称
from .....
,直接在select子句中用子查询不就好了吗?挺方便的嘛,但要注意||号的处理及子查询返回多条记录的问题
FROM person
WHERE b.name1 = ID AND ROWNUM < 2)
|| '|'
|| (SELECT firstname || ' ' || lastname
FROM person
WHERE b.name2 = ID AND ROWNUM < 2)
|| '|'
|| (SELECT firstname || ' ' || mlastname
FROM person
WHERE b.name3 = ID AND ROWNUM < 2)
|| '|'
|| (SELECT firstname || ' ' || lastname
FROM person
WHERE b.name4 = ID AND ROWNUM < 2)
|| '|'
|| (SELECT firstname || ' ' || lastname
FROM person
WHERE b.name5 = ID AND ROWNUM < 2),
'|'
),
'|'
) 名称
FROM person b;
(SELECT firstname||lastname FROM person where id = a.name1)||'|'||
(SELECT firstname||lastname FROM person where id = a.name2)||'|'||
(SELECT firstname||lastname FROM person where id = a.name3)||'|'||
(SELECT firstname||lastname FROM person where id = a.name4)||'|'||
(SELECT firstname||lastname FROM person where id = a.name5),'|')
FROM country a
(SELECT firstname||lastname FROM person where id = a.name1)||'|'||
(SELECT firstname||lastname FROM person where id = a.name2)||'|'||
(SELECT firstname||lastname FROM person where id = a.name3)||'|'||
(SELECT firstname||lastname FROM person where id = a.name4)||'|'||
(SELECT firstname||lastname FROM person where id = a.name5)
,'|'),'|')
FROM country a
(SELECT firstname||lastname FROM person where id = a.name1)||'|'||
(SELECT firstname||lastname FROM person where id = a.name2)||'|'||
(SELECT firstname||lastname FROM person where id = a.name3)||'|'||
(SELECT firstname||lastname FROM person where id = a.name4)||'|'||
(SELECT firstname||lastname FROM person where id = a.name5)
,'|'),'|')
FROM country a
这样写有问题啊.怎么出不来那样的结果呢
--建表
--drop table person
create table person(id numeric(5),FirstName varchar2(10),LastName varchar2(20))
insert into person(id,FirstName,LastName) values('1','David','Backham')
insert into person(id,FirstName,LastName) values('2','ED','Jason')
insert into person(id,FirstName,LastName) values('3','Nan','Xiao')
insert into person(id,FirstName,LastName) values('4','Liu','Endy')
insert into person(id,FirstName,LastName) values('5','Wang','Joe')
insert into person(id,FirstName,LastName) values('6','Chang','Lucy')select * from person
--drop table Country
create table Country(Country varchar2(20),name1 numeric(5),name2 varchar2(5),name3 numeric(5),name4 numeric(5),name5 numeric(5))
insert into Country(country,name1,name2,name3,name4,name5) values ('巴西',1,2,null,null,null)
insert into Country(country,name1,name2,name3,name4,name5) values ('武漢',3,4,5,6,null)select * from Country--函數
CREATE OR REPLACE function SYSTEM.fn_a(p_id integer)
return varchar2
is
v_s1 varchar2(1000);
v_FirstName varchar2(10);
v_LastName varchar2(20);
begin
v_s1:='';
if p_id>0 then
for rec in (select * from person where id=p_id)
loop
v_FirstName:=rec.FirstName;
v_LastName:=rec.LastName;
end loop;
v_s1:=v_FirstName || ' ' || v_LastName;
end if;
return v_s1;
end;
--結果
select country,substr(name,1,length(name)-1 )
from
(select country,
fn_a(name1)|| case when name1>0 then '|' else '' end ||
fn_a(name2)|| case when name2>0 then '|' else '' end ||
fn_a(name3)|| case when name3>0 then '|' else '' end ||
fn_a(name4)|| case when name4>0 then '|' else '' end ||
fn_a(name5)|| case when name5>0 then '|' else '' end
name
from Country
)A