mysql - 在 MySQL 中创建一个将数据显示为报告的查询

Question

我想知道是否可以在 MySql 中创建一个将以下数据显示为报告的查询。我有两张桌子：

部门

+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
+--------+------------+

雇员

+-------+--------+--------+
| empno | ename  | deptno |
+-------+--------+--------+
|  7782 | CLARK  |     10 |
|  7839 | KING   |     10 |
|  7934 | MILLER |     10 |
|  7369 | SMITH  |     20 |
|  7566 | JONES  |     20 |
+-------+--------+--------+

我有以下查询显示部门的名称及其员工：

SET @deptno = 10;
SELECT *
FROM (SELECT dname, '' AS Employees FROM dept WHERE deptno = @deptno
UNION SELECT '', ename FROM emp  WHERE deptno = @deptno)
AS tabla;

+------------+-----------+
| dname      | Employees |
+------------+-----------+
| ACCOUNTING |           |
|            | CLARK     |
|            | KING      |
|            | MILLER    |
+------------+-----------+

但是，我想展示所有部门。我不知道如何在查询中包含所有部门，如下所示：

+------------+-----------+
| dname      | Employees |
+------------+-----------+
| ACCOUNTING |           |
|            | CLARK     |
|            | KING      |
|            | MILLER    |
| RESEARCH   |           |
|            | SMITH     |
|            | JONES     |
+------------+-----------+

任何想法我都会很感激。谢谢 =)

标签： mysql

Answer 1

SELECT CASE WHEN dname = ename THEN dname ELSE '' END Department,
       CASE WHEN dname = ename THEN '' ELSE ename END Employee
FROM ( SELECT dname, ename FROM dept NATURAL JOIN emp
       UNION ALL 
       SELECT dname, dname FROM dept ) subquery
ORDER BY dname, dname != ename, ename

小提琴