时间戳

首页 > 解决方案 > 基于一个公共列连接具有不同列和格式的两个表 - 需要显示重复项

mysql - 基于一个公共列连接具有不同列和格式的两个表 - 需要显示重复项

问题描述

我有两个表显示关于同一 ID 的不同信息，这也是两个表中唯一的公共列。

我需要编译一个表：

我运行了以下内容：

SELECT * FROM mistakes
LEFT JOIN Delivery ON mistakes.id = Delivery.id

UNION

SELECT * FROM mistakes
RIGHT JOIN Delivery ON mistakes.id = Delivery.id 
WHERE delivery_status = "Completed";

补充：数据表：

错误事件表：

id  event_type  Employee_id
eedbdc4a    Mistake 28060
ee46766e    Mistake 24138
ecf7b502    Mistake 29761
ebb004b0    Mistake 27631
eedbdc4a    Mistake 32085
ee46766e    Mistake 31217
ecf7b503    Mistake 32085
ebb004b1    Mistake 7529
eedbdc4a    Mistake 4637
ee46766e    Mistake 11685

所有交货表：

request_id  accepted_ts Employee_id ride_status pickup_ts   dropoff_ts
33269979    04-06-17 8:05   10180   Completed   06-04-17 8:20   06-04-17 8:24
33270327    04-06-17 8:17   4637    No_answer   06-04-17 8:25   06-04-17 8:29
33270441    04-06-17 8:21   15632   Cancelled   06-04-17 8:33   06-04-17 8:36
33270465    04-06-17 8:22   10940   Completed   06-04-17 8:30   06-04-17 8:46
33270548    04-06-17 8:25   7264    No_answer   06-04-17 8:25   06-04-17 8:26
33270734    04-06-17 8:37   15632   Cancelled   06-04-17 8:42   06-04-17 8:48
33270770    04-06-17 8:44   24138   Completed   06-04-17 8:47   06-04-17 8:52
33270810    04-06-17 8:52   13851   No_answer   06-04-17 8:55   06-04-17 9:00
33270957    04-06-17 9:32   11561   Cancelled   06-04-17 9:33   06-04-17 9:38
33271104    10-22-17 10:20  7529    Completed   10-22-17 10:30  10-22-17 10:42
33271251    10-22-17 8:20   2322    No_answer   10-22-17 8:30   10-22-17 8:30
33271398    10-22-17 22:20  10345   Cancelled   10-22-17 22:56  10-22-17 22:56
33271545    10-22-17 10:24  28060   Completed   10-22-17 10:44  10-22-17 10:24
33271692    10-22-17 10:04  55231   No_answer   10-22-17 10:24  10-22-17 10:04
33271839    10-22-17 12:20  3453    Cancelled   10-22-17 12:50  10-22-17 12:20
33271986    10-22-17 2:20   19887   Completed   10-22-17 2:35   10-22-17 2:20
33272133    10-22-17 22:00  17342   No_answer   10-22-17 22:20  10-22-17 22:40
33272280    10-22-17 10:40  11685   Cancelled   10-22-17 11:02  10-22-17 10:40
33272427    10-22-17 18:20  29761   Completed   10-22-17 18:50  10-22-17 18:20
33272574    10-22-17 5:20   3443    No_answer   10-22-17 5:55   10-22-17 5:20
33272721    10-22-17 10:20  55221   Cancelled   10-22-17 10:30  10-22-17 10:20

标签： mysqlunion

解决方案

Where子句隐式转换RIGHT JOIN为INNER JOIN

SELECT * FROM mistakes
RIGHT JOIN Delivery ON mistakes.id = Delivery.id 
WHERE delivery_status = "Completed";

改为使用

SELECT * FROM mistakes
LEFT JOIN Delivery ON mistakes.id = Delivery.id
UNION
SELECT * FROM mistakes
RIGHT JOIN Delivery ON mistakes.id = Delivery.id 
       AND delivery_status = "Completed";

此查询将为您提供错误表中包含的记录，无论 Delivery 表中是否有相关数据，以及 Delivery 表中包含的记录，其状态等于 Completed 由错误连接，无论错误表中是否存在相关记录。

推荐阅读