有两个表,表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
请问哪位高人能给个正确答案啊
部门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
请问哪位高人能给个正确答案啊
from ( select * from a union select distinct 部门code as 部门code, 0 as 子部门code from a ) t1
left join b t2A on t1.部门code = t02A.部门code
left join b t2B on t1.子部门code = t02B.部门code
order by t1.部门code ,t1.子部门code;
b2.dep,
decode(b1.dcode,b2.dcode,null,b1.dep) as subdep
from (
select a1.sdcode,
a1.dcode
from a a1
union all
select distinct
a2.dcode as sdcode,
a2.dcode as dcode
from a a2
)aa,
b b1,
b b2
where aa.dcode = b2.dcode
and aa.sdcode = b1.dcode
order by 2,1;
result: SDCODE DEP SUBDEP
---------- ---- ------
2 dep2
5 dep2 ke1
6 dep2 ke2
3 dep3
7 dep3 ke1
8 dep3 ke2
4 dep4
9 dep4 ke1
10 dep4 ke29 rows selected
union
(select a.code1,e.name,d.name from A left join B D on a.code1=d.code left join b E on a.codeb=e.code)
b2.dep,
decode(b1.dcode,b2.dcode,null,b1.dep) as subdep
from (
select a1.sdcode,
a1.dcode
from a a1
union all
select distinct
a2.dcode as sdcode,
a2.dcode as dcode
from a a2
)aa,
b b1,
b b2
where aa.dcode = b2.dcode
and aa.sdcode = b1.dcode
order by 2,1研究研究
很好判断不是
表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
这样的话
问题就解决了
表B对表A的左外联接
然后对结果按照表B的部门name(字段)排序
自己试下
我没试
--Select
select b.code, b.NAME
from (select a.pcode, a.ccode
from a
connect by a.PCODE = prior a.CCODE
union
select b.CODE, b.code
from b
where exists (select * from a temp_a where temp_a.PCODE = b.CODE)) temp,
b
where b.CODE = temp.CCODE
order by temp.PCODE, temp.CCODE
union all
select distinct a.code1,b.name,t.name
from a,b,(select distinct a.code1,b.name,null dd from a,b where a.code2 = b.code) t
where a.code1 = b.code
and a.code1 = b.code
order by name
union all
select a.code1,b.name,b1.name
from a,b,b b1
where a.code1 = b.code
and a.code2 = b1.code
order by name
FROM a INNER JOIN
b AS b_1 ON a.pcode = b_1.code RIGHT OUTER JOIN
b ON a.ccode = b.code
ORDER BY ISNULL(b_1.code, b.code), b_1.code2 部门2 NULL
5 科1 部门2
6 科2 部门2
3 部门3 NULL
7 科1 部门3
8 科2 部门3
4 部门4 NULL
9 科1 部门4
10 科2 部门4 我这个是在Sql Server中做的,Oracle中要把ISNULL换成NVL
sql:
select bm, k
from (
select b1.name bm , b2.name k
from a a1,b b1, b b2
where a1.pcode=b1.code and a1.ccode=b2.code
) group by rollup (bm,k) order by bm desc
UNION ALL
SELECT A2.PCODE,B2.NAME ,B3.NAME FROM A A2,B B2,B B3 WHERE A2.PCODE=B2.CODE AND A2.CCODE=B3.CODE
#include<stdio.h>
int main()
{
int i = 0;
reutnr 0;
}