表数据为:
CREATE TABLE IT_research (deptno number, ename varchar2(20));
INSERT INTO it_research VALUES (100, 'HOPKINS');
INSERT INTO it_research VALUES (100,'JONES');
INSERT INTO it_research VALUES (100,'TONEY');
INSERT INTO it_research VALUES (200,'MORALES');
INSERT INTO it_research VALUES (200,'P.WHITAKER');
INSERT INTO it_research VALUES (200,'MARCIANO');
INSERT INTO it_research VALUES (200,'ROBINSON');
INSERT INTO it_research VALUES (300,'LACY');
INSERT INTO it_research VALUES (300,'WRIGHT');
INSERT INTO it_research VALUES (300,'J.TAYLOR');
CREATE TABLE IT_apps (deptno number, ename varchar2(20));
INSERT INTO it_apps VALUES (400, 'CORRALES');
INSERT INTO it_apps VALUES (400,'MAYWEATHER');
INSERT INTO it_apps VALUES (400,'CASTILLO');
INSERT INTO it_apps VALUES (400,'MARQUEZ');
INSERT INTO it_apps VALUES (400,'MOSLEY');
INSERT INTO it_apps VALUES (500,'GATTI');
INSERT INTO it_apps VALUES (500,'CALZAGHE');
INSERT INTO it_apps VALUES (600,'LAMOTTA');
INSERT INTO it_apps VALUES (600,'HAGLER');
INSERT INTO it_apps VALUES (600,'HEARNS');
INSERT INTO it_apps VALUES (600,'FRAZIER');
INSERT INTO it_apps VALUES (700,'GUINN');
INSERT INTO it_apps VALUES (700,'JUDAH');
INSERT INTO it_apps VALUES (700,'MARGARITO');
要求显示为
RESEARCH APPS
-------------------- --------
100 400
HOPKINS CASTILLO
JONES CORRALES
TONEY MARQUEZ
200 MAYWEATHER
MARCIANO MOSLEY
MORALES 500
P.WHITAKER CALZAGHE
ROBINSON GATTI
300 600
J.TAYLOR FRAZIER
LACY HAGLER
WRIGHT HEARNS
LAMOTTA
700
GUINN
JUDAH
MARGARITO
结果SELECT t1.ename, t2.ename
FROM (SELECT nvl(ename, deptno) AS ename, rownum AS rn
FROM (SELECT deptno, NULL AS ename
FROM it_research
GROUP BY deptno
UNION ALL
SELECT deptno, ename
FROM it_research
ORDER BY deptno, ename NULLS FIRST)) t1
FULL JOIN (SELECT nvl(ename, deptno) AS ename, rownum AS rn
FROM (SELECT deptno, NULL AS ename
FROM it_apps
GROUP BY deptno
UNION ALL
SELECT deptno, ename
FROM it_apps
ORDER BY deptno, ename NULLS FIRST)) t2
ON t1.rn = t2.rn
/
ENAMEENAME
--------------- ---------------
100400
HOPKINS CASTILLO
JONESCORRALES
TONEYMARQUEZ
200MAYWEATHER
MARCIANOMOSLEY
MORALES 500
P.WHITAKERCALZAGHE
ROBINSONGATTI
300600
J.TAYLORFRAZIER
LACYHAGLER
WRIGHTHEARNS
LAMOTTA
700
GUINN
JUDAH
MARGARITO
18 rows selected.