php - MySQL查询1个查询中不同计数的两个不同条件

Question

我有 3 个表名为

1. com_event_schedules
2. com_appointments
3. com_event_schedules_com_appointment_c

前两个表之间有关系。

以下是表格的字段

1. com_event_schedules - ID - 姓名-- schedule_date - 开始时间 - 时间结束-- 已删除

2. com_appointments - ID - 开始时间 - 时间结束-- 状态

3. com_event_schedules_com_appointment_c - ID-- com_event_schedules_com_appointmentcom_event_schedules_ida (schedule_id)-- com_event_schedules_com_appointmentcom_appointment_idb (appointment_id)

表 com_event_schedule 和 com_appointments 之间的关系是一对多

我想要的结果具有 schedule_id，并且条件 status='completed' 的约会总数

我尝试了以下查询:

SELECT sch.id,COUNT(app.id) AS total,
  (SELECT COUNT(ap.id) 
  FROM 
  com_appointment ap, 
  com_event_schedules sc, 
  com_event_schedules_com_appointment_c re 
  WHERE 
  re.com_event_schedules_com_appointmentcom_event_schedules_ida=sc.id AND  
  ap.id=re.com_event_schedules_com_appointmentcom_appointment_idb AND 
  sc.deleted=0 AND 
  ap.status='completed') AS completed

FROM 
com_event_schedules sch,
com_appointment app,
com_event_schedules_com_appointment_c rel 
WHERE 
rel.com_event_schedules_com_appointmentcom_event_schedules_ida=sch.id AND
app.id=rel.com_event_schedules_com_appointmentcom_appointment_idb AND 
sch.deleted=0 GROUP BY sch.id

使用此查询我得到了准确的总计数，但完成的计数与预期不符。它为每个时间表显示 1 个。然而，数据库中只有 1 个约会已完成，其他人仍在等待中。

查询有问题吗？？我在后端有 SugarCRM。不能使用 fiddle 导致关系和字段太乱。

Answer 1

这个查询应该能帮到你。它所做的最重要的事情是计算所有约会的总数，然后在 IF status = completed 上进行 SUM，以便在同一查询中同时获得总数和已完成。

SELECT
    sc.id,
    COUNT(ap.id) as total,
    SUM(IF(status = 'completed', 1, 0)) as completed
FROM
    com_event_schedules sc
LEFT JOIN
    com_event_schedules_com_appointment_c re
    ON re.com_event_schedules_com_appointmentcom_event_schedules_ida = sc.id
LEFT JOIN
    com_appointment ap
    ON re.com_event_schedules_com_appointmentcom_appointment_idb = ap.id
WHERE
    sc.deleted = 0
GROUP BY
    sc.id

此外，我注意到您说这是一对多关系。像您这样的关系表确实适用于多对多。拥有一对多的最有效方法是摆脱 com_event_schedules_com_appointment_c表并添加一个 com_event_schedule_id到 com_appointments表。