一道有点难度的oracle面试问题(sql查询语句)

Question

有两个表，表a有两个字段，分别是部门code和子部门code，表b有两个字段，部门code，和部门name 表a如下：                                                   表b： 
部门code     子部门code                       部门code       部门name 
2                   5                                       2                   部门2   
2                   6                                       3                   部门3 
3                   7                                       4                   部门4 
3                   8                                       5                   科1 
4                   9                                       6                   科2 
4                   10                                     7                   科1 
                                                            8                   科2 
                                                            9                   科1   
                                                            10                 科2 想输出这样的结果：   
2     部门2 
5     部门2   科1   
6     部门2   科2   
3     部门3   
7     部门3   科1   
8     部门3   科2   
4     部门4   
9     部门4   科1   
10   部门4   科2 
请问哪位高人能给个正确答案啊

Answer 1

首先要确定部门是不是只有两级，即子部门不再有子部门，否则无法形成你想要的结果。
下面是我试验的sql，感觉不是很好，楼下高手补充纠正。 select mid.depcode,b.depname,decode(mid.depname,b.depname,null,mid.depname) 
from (select b.depcode depcode,
nvl((select a.depcode from a where a.depsubcode=b.depcode),b.depcode) parentDepCode
,b.depname depname
from b) mid
left join b on mid.parentDepCode=b.depcode
order by mid.depcode
                                          

Answer 2

这样可能也行：
select aa.code,bb.names,null
from a aa,b bb
where aa.code=bb.code
union
select aa.sub_code,bb.names,cc.names
from a aa,b bb,b cc
where aa.code=bb.code
and aa.sub_code=cc.code

Answer 3

blackteal 的方法可能是在 oracle 里用的..所以我在 sql server 里不能试...
但 vc555 的方法好像没有得到想要的结果:
code        names            
code        names            
----------- ---------------- ----------------
2           部门2              NULL
3           部门3              NULL
4           部门4              NULL
5           部门2              部门2科1
6           部门2              部门2科2
7           部门3              部门3科1
8           部门3              部门3科2
9           部门4              部门4科1
10          部门4              部门4科2(9 行受影响)
我觉得应该是：
select distinct a.code, b.code, b.names from a left join b on a.code = b.code or a.sub_code = b.code以下是sql server 2005示例：
drop table a
create table a(code int not null , sub_code int not null)
drop table b
create table b(code int not null , names nvarchar(16) not null)
insert a values(2, 5)
insert a values(2, 6)
insert a values(3, 7)
insert a values(3, 8)
insert a values(4, 9)
insert a values(4, 10)/*
insert b values(2, N'部门2')
insert b values(3, N'部门3')
insert b values(4, N'部门4')
insert b values(5, N'科1')
insert b values(6, N'科2')
insert b values(7, N'科1')
insert b values(8, N'科2')
insert b values(9, N'科1')
insert b values(10, N'科2')
*/insert b values(2, N'部门2')
insert b values(3, N'部门3')
insert b values(4, N'部门4')
insert b values(5, N'部门2科1')
insert b values(6, N'部门2科2')
insert b values(7, N'部门3科1')
insert b values(8, N'部门3科2')
insert b values(9, N'部门4科1')
insert b values(10, N'部门4科2')select   aa.code,bb.names,null 
from   a   aa,b   bb 
where   aa.code=bb.code 
union 
select   aa.sub_code,bb.names,cc.names 
from   a   aa,b   bb,b   cc 
where   aa.code=bb.code 
and   aa.sub_code=cc.codeselect distinct a.code, b.code, b.names from a left join b on a.code = b.code or a.sub_code = b.code
上述两条 sql 的结果:code        names            
----------- ---------------- ----------------
2           部门2              NULL
3           部门3              NULL
4           部门4              NULL
5           部门2              部门2科1
6           部门2              部门2科2
7           部门3              部门3科1
8           部门3              部门3科2
9           部门4              部门4科1
10          部门4              部门4科2(9 行受影响)code        code        names
----------- ----------- ----------------
2           2           部门2
2           5           部门2科1
2           6           部门2科2
3           3           部门3
3           7           部门3科1
3           8           部门3科2
4           4           部门4
4           9           部门4科1
4           10          部门4科2(9 行受影响)

Answer 4

再进一步的 sql 语句为：select s.code, b.names , s.names from (select distinct a.code as dep, b.code, b.names from a left join b 
on a.code = b.code or a.sub_code = b.code) as s left join b on b.code = s.dep
以下是sql   server   2005示例： drop table a
create table a(code int not null , sub_code int not null)
drop table b
create table b(code int not null , names nvarchar(16) not null)
insert a values(2, 5)
insert a values(2, 6)
insert a values(3, 7)
insert a values(3, 8)
insert a values(4, 9)
insert a values(4, 10)/*
insert b values(2, N'部门2')
insert b values(3, N'部门3')
insert b values(4, N'部门4')
insert b values(5, N'科1')
insert b values(6, N'科2')
insert b values(7, N'科1')
insert b values(8, N'科2')
insert b values(9, N'科1')
insert b values(10, N'科2')
*/insert b values(2, N'部门2')
insert b values(3, N'部门3')
insert b values(4, N'部门4')
insert b values(5, N'部门2科1')
insert b values(6, N'部门2科2')
insert b values(7, N'部门3科1')
insert b values(8, N'部门3科2')
insert b values(9, N'部门4科1')
insert b values(10, N'部门4科2')
select s.code, b.names , s.names from (select distinct a.code as dep, b.code, b.names from a left join b 
on a.code = b.code or a.sub_code = b.code) as s left join b on b.code = s.dep上述   sql   的结果: code        names            names
----------- ---------------- ----------------
2           部门2              部门2
5           部门2              部门2科1
6           部门2              部门2科2
3           部门3              部门3
7           部门3              部门3科1
8           部门3              部门3科2
4           部门4              部门4
9           部门4              部门4科1
10          部门4              部门4科2(9 行受影响)

Answer 5

最后要的到楼主的结果，先来个笨点的方法：select s.code, b.names , case when s.dep <> s.code then s.names end from (
select distinct a.code as dep, b.code, b.names from a left join b 
on a.code = b.code or a.sub_code = b.code) as s left join b on b.code = s.dep查询结果：code        names            
----------- ---------------- ----------------
2           部门2              NULL
5           部门2              部门2科1
6           部门2              部门2科2
3           部门3              NULL
7           部门3              部门3科1
8           部门3              部门3科2
4           部门4              NULL
9           部门4              部门4科1
10          部门4              部门4科2(9 行受影响)

Answer 6

到底哪个查询更快？
以下 2 条sql语句返回相同的结果：select * from a left join b on a.code = b.code or a.sub_code = b.codeselect * from a left join b on a.code = b.code union select * from a left join b on a.sub_code = b.code 
经测试，第一次的查询开销为总开销的 21%，第 2 条的查询开销为总开销的 79%。

Answer 7

to : vc555 
是啊...所以你那查询我试了以下不是啊.我在 sql server 里试的
你在 oracle里执行的是lz要的结果?

Answer 8

另外一条使用 group by 的可以得到楼主结果的sql:select s.code, c.names, b.names from (select b.code, a.code as dep from a left join b 
on a.code = b.code or a.sub_code = b.code group by a.code, b.code) as s left join b 
on b.code = s.code and s.code <> s.dep left join b as c on c.code = s.dep

Answer 9

综合出 3 条sql语句:
select s.code, b.names , case when s.dep <> s.code then s.names end from (
select distinct a.code as dep, b.code, b.names from a left join b 
on a.code = b.code or a.sub_code = b.code) as s left join b on b.code = s.depselect s.code, c.names, b.names from (select b.code, a.code as dep from a left join b 
on a.code = b.code or a.sub_code = b.code group by a.code, b.code) as s left join b 
on b.code = s.code and s.code <> s.dep left join b as c on c.code = s.depselect s.code , c.names, b.names from (
select distinct a.code as dep, b.code, b.names from a left join b 
on a.code = b.code or a.sub_code = b.code) as s left join b 
on b.code = s.code and s.code <> s.dep left join b as c on c.code = s.dep
哪条最快?
经测试，第 1 条的查询开销为总开销的 30%，第   2   条的查询开销为总开销的   35%，第   3   条的查询开销为总开销的   35%。
原因 表达式计算最快啊.后面两条多了一次 join 所以慢了一点

Answer 10

SELECT b.code,nvl(bm.NAME,b.name) 部门,bs.NAME 子部门
  FROM SH_TEST_B B
  LEFT JOIN SH_TEST_A A ON B.CODE = A.SUB_CODE
  LEFT JOIN sh_test_b bm ON bm.code=a.code
  LEFT JOIN SH_TEST_B Bs ON A.SUB_CODE = Bs.CODE
  ORDER BY 部门,子部门
  

Answer 11

 (select distinct a.code,b.name as codename,' ' as name from a,b
 where a.code=b.code)
 union all
 (select sub_code, '部门'||a.code as codename,name from a,b
 where a.sub_code=b.code)
 order by codename

Answer 12

select distinct b.code,
case when b.code in (select a.code from a a) then b.names else 
(select c.names from (select a.*,b.names from a left  join b on a.code=b.code) c where c.sub_code=b.code)
 end name1,
case when b.code not in(select a.code from a a) then b.names else '' end name2
from b b试过 OK