有两张表，表A是主表，表B是从表，如下：
A:
A_ID A_content
A1 工作布置1
A2 工作布置2
A3 工作布置3
B：
B_ID B_FKNR B_TIME F_A_ID
B1 发规部已完成 2014-08-25 A1
B2 思政部已完成 2014-08-23 A1
B3 总经部已完成 2014-08-24 A1
B4 设备部已完成 2014-08-25 A2
B5 思政部已完成 2014-08-23 A2
B6 总经部已完成 2014-08-01 A3
要求展示格式为，能否一条语句出来下面的结果：
1、A1 工作布置1
B1 发规部已完成 2014-08-25 A1
B2 思政部已完成 2014-08-23 A1
B3 总经部已完成 2014-08-24 A1
2、A2 工作布置2
B4 设备部已完成 2014-08-25 A2
B5 思政部已完成 2014-08-23 A2
3、A3 工作布置3
B6 总经部已完成 2014-08-01 A3
------解决思路----------------------
SELECT TEXT FROM
(select row_number()over(order by A_ID)
------解决思路----------------------
'、'
------解决思路----------------------
A_ID
------解决思路----------------------
CHR(9)
------解决思路----------------------
A_content TEXT,A_ID,NULL TIME FROM A
UNION ALL
SELECT B_ID
------解决思路----------------------
CHR(9)
------解决思路----------------------
B_FKNR
------解决思路----------------------
CHR(9)
------解决思路----------------------
B_TIME
------解决思路----------------------
CHR(9)
------解决思路----------------------
F_A_ID TEXT,F_A_ID,B_TIME TIME FROM B)
ORDER BY A_ID,TIME DESC
------解决思路----------------------
with A as
(
select 'A1' A_ID,'GZBZ1' A_CONTENT FROM DUAL UNION ALL
select 'A2' A_ID,'GZBZ2' A_CONTENT FROM DUAL UNION ALL
select 'A3' A_ID,'GZBZ3' A_CONTENT FROM DUAL
),
B AS
(
SELECT 'B1' B_ID,'S1' B_FKNR,SYSDATE B_TIME,'A1' F_A_ID FROM DUAL UNION ALL
SELECT 'B2' B_ID,'S2' B_FKNR,SYSDATE B_TIME,'A1' F_A_ID FROM DUAL UNION ALL
SELECT 'B3' B_ID,'S3' B_FKNR,SYSDATE B_TIME,'A1' F_A_ID FROM DUAL UNION ALL
SELECT 'B4' B_ID,'S4' B_FKNR,SYSDATE B_TIME,'A2' F_A_ID FROM DUAL UNION ALL
SELECT 'B5' B_ID,'S5' B_FKNR,SYSDATE B_TIME,'A2' F_A_ID FROM DUAL UNION ALL
SELECT 'B6' B_ID,'S6' B_FKNR,SYSDATE B_TIME,'A3' F_A_ID FROM DUAL
)
SELECT * FROM(
SELECT DISTINCT A.A_ID C1,A.A_CONTENT C2,NULL C3,NULL C4 FROM A,B WHERE A.A_ID=B.F_A_ID
UNION ALL
SELECT B.B_ID C1,B.B_FKNR C2,B_TIME C3,F_A_ID C4 FROM B,A WHERE A.A_ID=B.F_A_ID)
ORDER BY DECODE(C4,NULL,C1,C4),C3 DESC;