首页 > 代码库 > 给两次转置的结果集增加列头
给两次转置的结果集增加列头
表数据为: 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 / ENAME ENAME --------------- --------------- 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 18 rows selected.
本文出自 “专注于Oracle性能调优” 博客,谢绝转载!
给两次转置的结果集增加列头
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。