如何基于首个记录进行关联？MySQL多表查询技术问询

数据老陈

2026-5-25

MySQL查询方案：关联邀请、通知与预约表

Hey there! I get that complex MySQL queries can feel tricky at first—let's walk through how to pull the info you need from your three tables. First, I'll assume your appointments table has an inv_id column linking it to the invites table (if that's not the actual column name, just swap it out for whatever you're using!).

1. 统计每个邀请的通知数和预约数

If you want a summary of each invite along with how many notifications and appointments are tied to it, this query will do the trick. We use LEFT JOIN to make sure even invites with no notifications or appointments show up in the results:

SELECT 
    i.id AS invite_id,
    i.name,
    i.created,
    COUNT(n.id) AS notification_count,
    COUNT(a.id) AS appointment_count
FROM invites i
LEFT JOIN notifications n ON i.id = n.inv_id
LEFT JOIN appointments a ON i.id = a.inv_id
GROUP BY i.id, i.name, i.created;

关键说明：

LEFT JOIN ensures every invite is included, even if it has no linked notifications or appointments.
COUNT(n.id) (instead of COUNT(*)) counts only actual notifications—since n.id will be NULL for invites with no notifications, those won't be counted. Same logic applies to COUNT(a.id) for appointments.

2. 列出每个邀请的具体通知和预约ID

If you need to see exactly which notification/appointment IDs are associated with each invite, use GROUP_CONCAT to list them out:

SELECT 
    i.id AS invite_id,
    i.name,
    i.created,
    GROUP_CONCAT(n.id SEPARATOR ', ') AS linked_notification_ids,
    GROUP_CONCAT(a.id SEPARATOR ', ') AS linked_appointment_ids
FROM invites i
LEFT JOIN notifications n ON i.id = n.inv_id
LEFT JOIN appointments a ON i.id = a.inv_id
GROUP BY i.id, i.name, i.created;