SQL高手请进，这个题目是一个外企的面试题目！

Question

第一个表(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
--------------------------------------------------------希望高手(大牛人)能够帮我，谢谢！！！           

Answer 1

写了一个，有点复杂，呵呵
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应该还可以优化，时间有限，:)

Answer 2

select 国家,rtrim(ltrim((select max(FirstName)||' '||max(LastName) from Person where b.name1=id)||'|'||(select max(FirstName)||' '||max(LastName) from Person where b.name2=id)||'|'||(select max(FirstName)||' '||max(LastName) from Person where b.name3=id)||'|'||(select max(FirstName)||' '||max(LastName) from Person where b.name4=id)||'|'||(select max(FirstName)||' '||max(LastName) from Person where b.name5=id),'|'),'|')  名称   
from Person b;

Answer 3

这里的max没有任何的意义，只是用来让ID对应唯一的记录。

Answer 4

直接查询就可以了
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

Answer 5

select   国家, p1.firstname||' '||p1.lastname
       ||'|'|| 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

Answer 6

select 国家,firstname || lastname from table1 a,table2 b where a.id = b.name1 or a.id= b.name2 or a.id =b.name3 or a.id = b.name4 or a.id = b.name5

Answer 7

Create Table A (Id Number , FirstName Nvarchar2(10) , LastName Nvarchar2(10));Create Table B ( C nvarchar2(10) , name1 number , 
                                   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

Answer 8

算法：把第一个表(Person)别名5次（即第二个表的name1~name5使用）：
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) 

Answer 9

完善一点：
 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 .....

Answer 10

同意lyjun_311(流浪天下) 
，直接在select子句中用子查询不就好了吗？挺方便的嘛，但要注意||号的处理及子查询返回多条记录的问题

Answer 11

SELECT 国家,RTRIM (LTRIM (   (SELECT firstname || ' ' || lastname
                           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;

Answer 12

SELECT country, RTRIM(
                (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

Answer 13

SELECT country, LTRIM(RTRIM(
                (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

Answer 14

SELECT country, LTRIM(RTRIM(
                (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
这样写有问题啊.怎么出不来那样的结果呢

Answer 15

--建表
--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