我有4个表，如下所示
表：leave_request
+------------+----------+--------------+------------+----------------------+
| request_id | staff_id | applied_from | applied_to | status |
+------------+----------+--------------+------------+----------------------+
| 1 | 10 | 01-07-2012 | 02-07-2012 | approved |
| 2 | 12 | 02-07-2012 | 02-07-2012 | awaiting HR approval |
+------------+----------+--------------+------------+----------------------+
表：离职审批
+-------------+-------------+---------------+-------------+
| request_id | approved_by | approved_from | approved_to |
+-------------+-------------+---------------+-------------+
| 1 | 1 | 01-07-2012 | 02-07-2012 |
| 1 | 2 | 01-07-2012 | 02-07-2012 |
| 2 | 1 | 02-07-2012 | 02-07-2012 |
+-------------+-------------+---------------+-------------+
表：员工
+-----------+-------+----------+
| staff_id | name | group_id |
+-----------+-------+----------+
| 1 | jack | 1 |
| 2 | jill | 2 |
| 10 | sam | 3 |
| 12 | david | 3 |
+-----------+-------+----------+
表：组
+-----------+------------+
| group_id | group_name |
+-----------+------------+
| 1 | admin |
| 2 | HR |
| 3 | staff |
+-----------+------------+
我需要通过加入这些表格来做一个报告，它应该如下所示：
+----------+------------+----------+-------------+-----------+--------------+-----------+
|applied_by|applied_from|applied_to|approved_from|approved_to|approved_admin|approved_hr|
+----------+------------+----------+-------------+-----------+--------------+-----------+
| sam | 01-07-2012 |02-07-2012|01-07-2012 |02-07-2012 | Jack | Jill |
| david | 02-07-2012 |02-07-2012|02-07-2012 |02-07-2012 | Jack | null |
+----------+------------+----------+-------------+-----------+--------------+-----------+
提前感谢：）
让我们循序渐进。。。
首先，要选择的实体位于leave_request表中。让我们从这里开始：
SELECT leave_request.* FROM leave_request
现在，您需要知道所需结果中applied_by列的数据。所以你加入了staff表格：
SELECT
applied_staff.name AS applied_by
FROM
leave_request
INNER JOIN staff AS applied_staff ON leave_request.staff_id = applied_staff.staff_id
（请注意，我使用别名作为表名。这将在稍后派上用场。）
现在您需要知道applied_from和applied_to，它们已经可用：
SELECT
applied_staff.name AS applied_by,
leave_request.applied_from,
leave_request.applied_to
FROM
leave_request
INNER JOIN staff AS applied_staff ON leave_request.staff_id = applied_staff.staff_id
现在您需要知道approved_from和approved_to，它们在leave_approval表中：
SELECT
applied_staff.name AS applied_by,
leave_request.applied_from,
leave_request.applied_to,
admin_approval.approved_from,
admin_approval.approved_to
FROM
leave_request
INNER JOIN staff AS applied_staff ON leave_request.staff_id = applied_staff.staff_id
INNER JOIN leave_approval AS admin_approval ON leave_request.request_id = admin_approval.request_id
哦，现在我们有问题了。这是一种一对多的关系，所以现在我们在结果中有重复的休假请求。我们需要以某种方式把它过滤掉。您没有指定如何进行，所以我将做几个假设：您想知道"管理员"审批的approved_from和approved_to，并且只有一个"管理员"批准
让我们在联接表中反映这些假设：
SELECT
applied_staff.name AS applied_by,
leave_request.applied_from,
leave_request.applied_to,
admin_approval.approved_from,
admin_approval.approved_to
FROM
leave_request
INNER JOIN staff AS applied_staff ON leave_request.staff_id = applied_staff.staff_id
INNER JOIN leave_approval AS admin_approval ON leave_request.request_id = admin_approval.request_id
INNER JOIN staff AS approved_staff ON admin_approval.approved_by = approved_staff.staff_id
INNER JOIN group AS approved_staff_group on approved_staff.group_id = approved_staff_group.group_id
WHERE
approved_staff_group.group_name = 'admin'
那应该更好。请注意，表别名在这里很有用，因为我们现在在同一查询中有两个staff表实例用于两个不同的目的。所以我们需要区分它们。（请记住，我在这里是盲目的，实际上无法测试任何这些。因此，如果在这一过程中遇到任何问题，请纠正我。我也可以免费处理这些代码，因为我手头没有MySQL，所以如果有语法错误，请告诉我。）
现在，让我们将approved_admin字段添加到结果中，该字段已经可用：
SELECT
applied_staff.name AS applied_by,
leave_request.applied_from,
leave_request.applied_to,
admin_approval.approved_from,
admin_approval.approved_to,
approved_staff.name AS approved_admin
FROM
leave_request
INNER JOIN staff AS applied_staff ON leave_request.staff_id = applied_staff.staff_id
INNER JOIN leave_approval AS admin_approval ON leave_request.request_id = admin_approval.request_id
INNER JOIN staff AS approved_staff ON admin_approval.approved_by = approved_staff.staff_id
INNER JOIN group AS approved_staff_group on approved_staff.group_id = approved_staff_group.group_id
WHERE
approved_staff_group.group_name = 'admin'
最后，我们需要知道approved_hr。null是允许的吗？那么，我们将对此使用不同的联接。我也在做与上面类似的假设。让我们试试这个：
SELECT
applied_staff.name AS applied_by,
leave_request.applied_from,
leave_request.applied_to,
admin_approval.approved_from,
admin_approval.approved_to,
approved_staff.name AS approved_admin,
hr_staff.name AS approved_hr
FROM
leave_request
INNER JOIN staff AS applied_staff ON leave_request.staff_id = applied_staff.staff_id
INNER JOIN leave_approval AS admin_approval ON leave_request.request_id = admin_approval.request_id
INNER JOIN staff AS approved_staff ON admin_approval.approved_by = approved_staff.staff_id
INNER JOIN group AS approved_staff_group on approved_staff.group_id = approved_staff_group.group_id
LEFT OUTER JOIN leave_approval AS hr_approval ON leave_request.request_id = hr_approval.request_id
LEFT OUTER JOIN staff AS hr_staff ON hr_approval.approved_by = hr_staff.staff_id
LEFT OUTER JOIN group AS hr_staff_group ON hr_staff.group_id = hr_staff_group.group_id
WHERE
approved_staff_group.group_name = 'admin'
AND hr_staff_group.group_name = 'HR'
我对后一个LEFT OUTER JOIN s不太确定。第一个肯定需要一个允许null值的联接，但我不确定查询引擎如何处理除此之外的联接。我希望它们是初始LEFT OUTER JOIN范围内的INNER JOIN。但我想所有这些实际上也取决于数据的完整性，我不能保证。
同样值得注意的是，当值为"jack"时，您声称希望"Jack"作为输出。我在这段代码中没有做任何字符串操作来实现这一点。如果该值应在数据中大写，则在数据中将其大写。
同样，我不能保证这个代码。但作为一次穿越，它应该会让你朝着正确的方向前进。正如我在对这个问题的评论中提到的，如果你要写MySQL代码，我真的建议你读一本关于MySQL的书。
编辑：我可以给出的一个建议是数据本身的结构。具体来说，leave_approval表感觉有点乱，而正是这张表造成了混乱。我有几个建议：
- 在
leave_approval表中添加一个approval_type。这至少表明这是行政批准、人力资源批准还是任何其他类型的批准。（还有其他类型吗？会有吗？）然后，您也可以使用request_id和approval_type作为组合主键，或者至少是组合唯一约束，以增强数据完整性并防止重复审批 - 如果只有两种批准，并且这种情况可能不会改变，请在
leave_approval表中反映这两种批准。为admin_approval_*和hr_approval_*分别设置一组列。（每组将包括staff_id和批准的相关日期。）然后request_id本身可以是leave_approval上的主键，使其与leave_request一一对应。这将极大地简化关系数据，实质上将leave_approval记录变成leave_request记录的可选附加信息集。连接将变得更加简单，数据将更加清晰地表达自己