时间戳

首页 > 解决方案 > 连接两个表并获取重复行

mysql - 连接两个表并获取重复行

问题描述

我有两个表，每个表有两个共同的列（ID 和日期）。我想从表 1 中获取所有行，从表 2 中获取状态列，然后还添加另一列用于显示是否有约会的标志。我能够在不加入两个表并使用 EXIST 的情况下添加标志列，但是当我在 ID 上加入 2 个表以添加状态列时，当我应该只得到 18 行时，我在输出中得到了额外的行。

表格1

ID, DateYM, Status
1,  2021-01-01, On
1,  2021-02-01, On
1,  2021-03-01, Off
1,  2021-04-01, On
1,  2021-05-01, On
1,  2021-06-01, On
2,  2021-01-01, Off
2,  2021-02-01, On
2,  2021-03-01, Off
2,  2021-04-01, On
2,  2021-05-01, On
2,  2021-06-01, On
3,  2021-01-01, On
3,  2021-02-01, Off
3,  2021-03-01, On
3,  2021-04-01, On
3,  2021-05-01, On
3,  2021-06-01, On

表 2

ID, Appt_date, State, Appointment
1,  2021-01-12, set, Registered
1,  2021-04-14, notset, Registered
1,  2021-05-13, notset, Registered
2,  2021-02-12, set, Registered
2,  2021-04-17, set, Registered
2,  2021-05-15, notset, Registered
2,  2021-06-12, set, Registered
3,  2021-03-19, notset, Registered
3,  2021-06-10, notset, Registered

预期结果表：

ID, DateYM, Status, Appointment, State
1,  2021-01-01, On, Yes, set
1,  2021-02-01, On, No, null
1,  2021-03-01, Off, No, null
1,  2021-04-01, On, Yes, notset
1,  2021-05-01, On, Yes, notset
1,  2021-06-01, On, No, null
2,  2021-01-01, Off, NO, null
2,  2021-02-01, On, Yes, set
2,  2021-03-01, Off, No, null
2,  2021-04-01, On, Yes, set
2,  2021-05-01, On, Yes, notset
2,  2021-06-01, On, Yes, set
3,  2021-01-01, On, No, null
3,  2021-02-01, Off, No, null
3,  2021-03-01, On, Yes, notset
3,  2021-04-01, On, No, null
3,  2021-05-01, On, No, null
3,  2021-06-01, On, Yes, notset

我用于mysql的查询：


    select *, state,
    case when exists (
     select * from t2 
     where t1.id=t2.id 
     and date_format(t1.dateym, "%Y-%m") = date_format(t2.appt_date, "%Y-%m") and 
     t2.appointment='Registered') then 'Yes' else 'No' end Appointment 
    from t1
    join t2 on t1.id=t2.id

对于给定的 ID，当我加入时，我会在同一个月获得多行，而不是每个 ID 只有 6 行。如何从表 1 中获取所有行以及从表 2 中获取 2 个新列？

例子

标签： mysql

解决方案

LEFT JOIN 两个带有格式化日期和 ID 的表

-- MySQL
SELECT t1.ID
     , t1.DateYM
     , t1.Status
     , (CASE WHEN t2.Appointment = 'Registered' then 'Yes' else 'No' end) as Appointment
     , t2.state
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID 
       and DATE_FORMAT(t1.DateYM, "%Y-%m") = DATE_FORMAT(t2.Appt_date, "%Y-%m")
ORDER BY t1.ID, t1.DateYM

请从网址https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ef715b567a2d9a47f9796923386352a6检查

推荐阅读