MySQL左连接重复行问题：如何获取各成员ID的月度状态及预约标记

The issue you're facing comes from two key problems in your original query:

1. Your LEFT JOIN condition tries to match exact dates (t1.DateYM = t2.Appt_date), but DateYM is the first day of the month while Appt_date is a specific day within that month—so this won’t match correctly for most cases.
2. Even if the date logic was fixed, a single ID can have multiple appointments in one month, which would cause duplicate rows from the join.

Here are two clean solutions to get your desired output:

Solution 1: Use EXISTS (Most Efficient) #

This checks if there’s at least one matching appointment for the ID and month, without creating duplicate rows:

SELECT 
    t1.ID, 
    t1.DateYM, 
    t1.Status, 
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM Table2 t2 
            WHERE t2.ID = t1.ID 
              -- Match same year and month, not exact date
              AND YEAR(t2.Appt_date) = YEAR(t1.DateYM)
              AND MONTH(t2.Appt_date) = MONTH(t1.DateYM)
              AND t2.Appointment = 'Registered'
        ) THEN 'Yes' 
        ELSE 'No' 
    END AS Appointment
FROM Table1 t1 
ORDER BY t1.DateYM, t1.ID;

How it works: #

• The EXISTS subquery stops searching as soon as it finds one valid appointment for the ID and month, making it efficient.
• We use YEAR() and MONTH() to compare the month/year between DateYM and Appt_date, instead of exact dates.

Solution 2: Pre-Aggregate Table2 with GROUP BY #

If you prefer using a join, first aggregate Table2 to get one row per ID per month, then join to Table1:

SELECT 
    t1.ID, 
    t1.DateYM, 
    t1.Status, 
    CASE 
        WHEN t2.HasAppointment IS NOT NULL THEN 'Yes' 
        ELSE 'No' 
    END AS Appointment
FROM Table1 t1 
LEFT JOIN (
    SELECT 
        ID,
        YEAR(Appt_date) AS ApptYear,
        MONTH(Appt_date) AS ApptMonth,
        1 AS HasAppointment
    FROM Table2 
    WHERE Appointment = 'Registered'
    GROUP BY ID, ApptYear, ApptMonth
) t2 ON t1.ID = t2.ID 
   AND YEAR(t1.DateYM) = t2.ApptYear
   AND MONTH(t1.DateYM) = t2.ApptMonth
ORDER BY t1.DateYM, t1.ID;

How it works: #

• The subquery groups Table2 by ID and month, creating a single row for each ID-month combination that has at least one registered appointment.
• Joining this aggregated result to Table1 ensures no duplicate rows from multiple appointments.

Both solutions will return exactly one row per ID per month, with the correct Yes/No flag for whether the member had a registered appointment that month.

内容的提问来源于stack exchange，提问作者Tina