连接3个返回混合结果的表(mysql)
连接3个返回混合结果的表(mysql)
提问于 2015-05-26 19:20:55
我有三张桌子：
阿尔塔·巴哈斯
这是一些示例数据：
EMPLEADOS
+-------------+------------+-------------+
| id_empleado | nombres | ap_paterno |
+-------------+------------+-------------+
| 1020 | JUAN | FUENTES |
+-------------+------------+-------------+
| 1021 | JOSE | FLORES |
+-------------+------------+-------------+阿尔塔
+-------------+--------------+-------------+-----------+
| id_alta | id_empleado | fecha_alta | tipo |
+-------------+--------------+-------------+-----------+
| 958 | 1020 | 2015-02-27 | ALTA |
+-------------+--------------+-------------+-----------+
| 961 | 1020 | 2015-02-26 | REINGRESO |
+-------------+--------------+-------------+-----------+
| 972 | 1020 | 2015-03-19 | REINGRESO |
+-------------+--------------+-------------+-----------+
| 1003 | 1020 | 2015-05-19 | REINGRESO |
+-------------+--------------+-------------+-----------+巴哈斯
+-------------+--------------+-------------+--------------+------------+
| id_baja | id_empleado | id_motivo | fecha_baja |finiquitado |
+-------------+--------------+-------------+--------------+------------+
| 896 | 1020 | 1 | 2015-03-12 | 0 |
+-------------+--------------+-------------+--------------+------------+
| 909 | 1020 | 1 | 2015-03-30 | 1 |
+-------------+--------------+-------------+--------------+------------+我使用以下查询：
SELECT
EMPLEADOS.id_empleado, ALTAS.id_alta,
ALTAS.fecha_alta, ALTAS.tipo,
BAJAS.id_baja, BAJAS.fecha_baja
FROM EMPLEADOS
LEFT JOIN ALTAS ON EMPLEADOS.id_empleado = ALTAS.id_empleado
LEFT JOIN BAJAS ON EMPLEADOS.id_empleado = BAJAS.id_empleado
WHERE EMPLEADOS.id_empleado = 1020
GROUP BY ALTAS.fecha_alta, BAJAS.fecha_baja
ORDER BY fecha_alta DESC, fecha_baja DESC我得到的结果是：
+-------------+---------+------------+----------+---------+------------+
| id_empleado | id_alta | fecha_alta | tipo | id_baja | fecha_baja |
+-------------+---------+------------+----------+---------+------------+
| 1020 | 1003 | 2015-05-19 | REINGRESO| 909 | 2015-03-30 |
+-------------+---------+------------+----------+---------+------------+
| 1020 | 1003 | 2015-05-19 | REINGRESO| 896 | 2015-03-12 |
+-------------+---------+------------+----------+---------+------------+
| 1020 | 972 | 2015-03-19 | REINGRESO| 909 | 2015-03-30 |
+-------------+---------+------------+----------+---------+------------+
| 1020 | 972 | 2015-03-19 | REINGRESO| 895 | 2015-03-12 |..。以此类推，多达8个结果(将所有ALTAS (4)与BAJAS (2)表信息混合)
当期望的结果是：
+-------------+---------+------------+----------+---------+------------+
| id_empleado | id_alta | fecha_alta | tipo | id_baja | fecha_baja |
+-------------+---------+------------+----------+---------+------------+
| 1020 | 1003 | 2015-05-19 | REINGRESO| | |
+-------------+---------+------------+----------+---------+------------+
| 1020 | | | | 909 | 2015-03-30 |
+-------------+---------+------------+----------+---------+------------+
| 1020 | 972 | 2015-03-19 | REINGRESO| | |...And等多达6个结果(4个ALTAS数据和2个BAJAS数据)
为什么我的查询是错误的？
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-05-26 19:38:29
您需要对每个表分别进行连接，然后将它们与UNION组合起来。使用NULL作为来自另一个表的列的占位符。
SELECT * FROM (
SELECT e.id_empleado, a.id_alta, a.fecha_alta, NULL AS id_baja, NULL AS fecha_baja
FROM EMPLEADOS AS e
LEFT JOIN ALTAS AS a ON e.id_empleado = a.id_empleado
WHERE e.id_empleado = 1020
UNION ALL
SELECT e.id_empleado, NULL, NULL, b.id_baja, b.fecha_baja
FROM EMPLEADOS AS e
LEFT JOIN BAJAS AS b ON e.id_empleado = b.id_empleado
WHERE e.id_empleado = 1020
) AS u
ORDER BY fecha_alta DESC, fecha_baja DESCStack Overflow用户
发布于 2015-05-26 19:57:50
建议:您需要在每个表中分别运行它，并使用union来追加数据。加入Altas时，将列值(来自bajas的列)保持为空，反之亦然。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30467110
复制相关文章
相似问题