我有兩個表，每個表有兩個共同的列（ID和日期）。我想從表1中獲取所有的行，從表2中獲取狀態列，然后再添加一個標志列，顯示是否有預約。我能夠在不連接兩個表和使用EXIST的情況下添加標志列，但是當我在ID上連接兩個表以添加狀態列時，我的輸出中得到了額外的行，而我應該只得到18行。
表1
ID, DateYM, Status
1, 2021-01-01, On.
1, 20210201, On.
1, 202103-01, 關
1, 2021-04-01, 開, 開。
1, 2021-05-01, On.
1, 2021-06-01, On.
2, 2021-01-01, 關
2, 2021-02-01, 開, 開。
2, 202103-01, 關
2, 2021-04-01, 開, 開。
2, 2021-05-01, On.
2, 2021-06-01, On.
3, 2021-01, On, On.
3, 20210201, 關
3, 2021-03-01, 開, 開。
3, 2021-04-01, On.
3, 2021-05-01, On.
3, 2021-06-01, On.
表2
ID, Appt_date, State, Appointment
1, 20210112, set, Registered
1, 2021-0414, notset, Registered
1, 2021-05-13, notset, Registered
2, 2021-02-12, set, Registered
2, 2021-04-17, set, Registered
2, 20210515, notset, Registered
2, 2021-06-12, set, Registered
3, 2021-0319, notset, Registered
3, 2021-06-10, Notset, Registered
預期結果表：
ID, DateYM, Status, Appointment, State
1, 2021-01, On, yes, set
1, 20210201, On, No, null.
1, 2021-03-01, Off, No, null.
1, 2021-04-01, 開啟, 是, 不設定
1, 2021-05-01, 開啟, 是, 不設定
1, 202106-01, On, No, null.
2, 2021-01, Off, NO, null.
2, 20210201, On, Yes, set.
2, 2021-03-01, 關閉, No, null.
2, 2021-04-01, On, Yes, set。
2, 2021-05-01, On, Yes, notset
2, 2021-06, On, Yes, set.
3, 202101-01, On, No, null.
3, 2021-02-01, Off, No, null.
3, 2021-03-01, 開, 是, notset
3, 202104-01, On, No, null.
3, 20210501, On, No, null.
3, 2021-06-01, 開啟, Yes, notset
我用于mysql的查詢：
select *, state,
case when exist (
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個新的列？
uj5u.com熱心網友回復：
LEFT JOIN兩個具有格式化日期和ID的表
-- MySQL
SELECT t1.ID
, t1.DateYM
, t1.狀態
, (CASE WHEN t2. = 'Registered'/span> 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
Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ef715b567a2d9a47f9796923386352a6
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/321933.html
標籤：