合并两个MySQL表而不重复(没有以前的主题提供的解决方案)

Question

我正处于理智的边缘。在发布我的问题之前，我已经四处寻找了可能的解决方案，但我找不到任何解决方案。

我有三张布局如下的桌子：

Employee                User_logs                        Assessment
|----------------| |----------------------|-------| |----------------------|-------|
| empID  | uName | | checkindate | status | FK_ID | | asDate     |saResult | FK_ID |
|----------------| |----------------------|-------| |----------------------|-------|
| 1      | Tino  | | 2020-04-04  | Clear  |   1   | | 2020-04-04 | Pass    |   1   |
| ...    | ...   | | 2020-05-05  | Fail   |   1   | | 2020-05-05 | Pass    |   1   |
| ...    | ...   | | 2020-07-07  | Fail   |   1   | | 2020-06-06 | Fail    |   1   |
| ...    | ...   | | 2020-08-08  | Clear  |   1   | | 2020-08-08 | Pass    |   1   |
| ...    | ...   | |------------------------------| | 2020-09-09 | Pass    |   1   |
|----------------|                                  |------------------------------|

empID是我在User_Logs和Assessment表中用作外键的主键。我想要的结果如下：

Result
| FK_ID  |   resultDate | Status| Result |
|----------------------------------------|
|   1    | 2020-04-04   | Clear | Pass   |
|   1    | 2020-05-05   | Fail  | Pass   |
|   1    | 2020-06-06   |       | Fail   |
|   1    | 2020-07-07   | Fail  |        |
|   1    | 2020-08-08   | Clear | Pass   |
|   1    | 2020-09-09   |       | Pass   |  <---- Not shown
|----------------------------------------|

我确实接近以下代码，但由于某种原因，它没有在2020-09-09的最后一个条目中出现：

SELECT A.checkindate, A.status, B.saDate, B.saResult
FROM myDatabase.User_logs AS A 
LEFT JOIN (
SELECT DISTINCT saDate, saResult, FK_empID
FROM myDatabase.assesment
) AS B 
ON  B.FK_empID = 1
AND B.saDate = A.checkindate
WHERE A.FK_empID = 1
ORDER BY A.checkindate DESC

我做错了什么或者没看到什么？

Answer 1

SELECT e.empID, d.resultDate, u.Status, a.saResult Result 
FROM Employee e
CROSS JOIN ( SELECT checkindate resultDate
FROM User_logs
UNION 
SELECT asDate
FROM Assessment ) d
LEFT JOIN User_logs u ON e.empID = u.FK_ID AND d.resultDate = u.checkindate
LEFT JOIN Assessment a ON e.empID = a.FK_ID AND d.resultDate = a.asDate
-- WHERE COALESCE(u.Status, a.saResult)
-- ORDER BY empID, resultDate

Answer 2

尝试一下改进这个：

SELECT * FROM
(
SELECT fk_id, checkInDate as d FROM user_logs WHERE fk_id = 1 
UNION 
SELECT fk_id, asDate FROM assessment WHERE fk_id = 1
) datelist
LEFT JOIN user_logs ul 
ON 
datelist.d = ul.checkindate AND 
datelist.fk_id = ul.fk_id
LEFT JOIN assessment a 
ON 
datelist.d = a.asdate AND 
datelist.fk_id = a.fk_id

基本概念是，我们为员工1生成一个唯一的日期列表，将日志和评估加入其中。根据定义，这些连接总是在一种或另一种情况下(可能两者都有(起作用，因为我们连接的东西来自最初的表

为了清楚起见，我将联接到Employee排除在查询之外-我试图向您解释为什么这有效，而您的无效-您可以将Employees表内部联接到日期列表中，并在SELECT 中安排您想要的列

---

就原始查询而言，它是logs left join assessment——这意味着因为日志中没有2020-09-09值，所以评估的2020-09-09行被丢弃；本质上，当进行左联接时，你对DB说，"；左边是我主要感兴趣的所有信息，我也想知道右边是否有匹配的信息你含蓄地告诉数据库你对2020-09-09行不感兴趣，结果

你可以把它翻转过来，这样评估就是"；主要是你感兴趣的"；然后只在匹配的情况下显示日志，但如果评估没有从日志中得到您想要的行，则可能会遇到同样的问题

FULL OUTER JOIN通常用于这样的事情——你说的是"我对双方的这些事情都同样感兴趣，如果对方有比赛，那就太好了。。如果没有，我就不会难过；

---

这个答案中提出的技术有点不同；我们建立了一个唯一的日期列表，它绝对是包罗万象的(它有所有的日志日期和所有的评估日期，然后删除重复的日期(，这样你就可以申请"；这绝对是我感兴趣的100%日期列表，如果有日志，那就太好了，如果有评估，那就好了，无论哪种情况，如果没有，那也没关系；