SQL查询以显示父表的记录以及其所有子表的记录 [英] SQL query to display a parent table's record followed by all its child table's record

问题描述

我需要写下一条SQL语句，该语句使用表EMP和DEPT显示以下输出.

I need to write down a single SQL statement that displays the following output by using tables EMP and DEPT.

输出:

DEPTNO DNAME            LOC           
---------- -------------- ------------- 
    10 ACCOUNTING     NEW YORK      

 EMPNO ENAME
---------- ----------
  7782 CLARK
  7839 KING
  7934 MILLER


    20 RESEARCH       DALLAS        
 EMPNO ENAME
---------- ----------
  7369 SMITH
  7566 JONES
  7788 SCOTT
  7876 ADAMS
  7902 FORD


    30 SALES          CHICAGO       
 EMPNO ENAME
---------- ----------
  7499 ALLEN
  7521 WARD
  7654 MARTIN
  7698 BLAKE
  7844 TURNER
  7900 JAMES

6 rows selected.

    40 OPERATIONS     BOSTON        


no rows selected

因此，我需要从查询中获得此特定输出，首先显示部门信息，然后显示子表中的所有相应记录.

So I need this specific output from a query where the dept information is shown first then all its respective records from the child table.

推荐答案

从我的角度来看，该任务并不像看起来那样简单.好的，我的SQL * Plus功能可能不如以前/应该的那样好.主要问题是如何使每个部门的EMP表标题重复(不，我不知道该怎么做).

From my point of view, that task isn't as simple as it looks. OK, my SQL*Plus capabilities probably aren't as good as they used to / should be. The main problem is how to make EMP table's headings repeat for each department (and no, I don't know how to do that).

我设法产生的最接近的结果是:

The closest result I managed to produce is this:

SQL> col deptno format 999999
SQL> col dname  format a10
SQL> col loc    format a10
SQL> set linesize 30
SQL> set recsep off
SQL> break on deptno on dname on loc
SQL>
SQL> select d.deptno, d.dname, d.loc, e.empno, e.ename
  2  from dept d left join emp e on e.deptno = d.deptno
  3  order by d.deptno;

 DEPTNO DNAME      LOC
------- ---------- ----------
     EMPNO ENAME
---------- ----------
     10 ACCOUNTING NEW YORK
      7839 KING

      7782 CLARK

      7934 MILLER
     20 RESEARCH   DALLAS
      7902 FORD

      7369 SMITH

      7566 JONES
     30 SALES      CHICAGO
      7900 JAMES

      7844 TURNER

      7654 MARTIN

      7521 WARD

      7499 ALLEN

      7698 BLAKE
     40 OPERATIONS BOSTON


13 rows selected.

一旦您，Enferno(或其他任何人)都能设法准确地获得所需的内容，我真的很想看看最终的代码.

Once you, Enferno (or anyone else) manages to get exactly what's been asked for, I'd really like to see the final code.

这篇关于SQL查询以显示父表的记录以及其所有子表的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！