MySQL查询1个查询中两个不同条件的不同计数 [英] MySQL Query two different conditions for different count in 1 query

问题描述

我有3个名为

1. com_event_schedules
2. com_appointments
3. com_event_schedules_com_appointment_c

在前两个表之间有关系.

which has a relation between first two tables.

以下是表格的字段

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

1. com_event_schedules -- id -- name -- schedule_date -- start_time -- end_time -- deleted

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

com_appointments -- id -- start_time -- end_time -- status

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_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之间的关系是1对多

relation between tables com_event_schedule and com_appointments is 1 to Many

我想要的结果具有schedule_id，以及条件状态为已完成"的约会的总次数

What I want result having schedule_id, and total counts of its appointments on condition 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.但是，db中只有1个约会已完成，其他约会仍在等待中.

Using this query Im getting accurate total count but completed count is not as expected. it is showing 1 for each schedule. However only 1 appointment in db is completed and others are still pending.

查询是否有问题?? 我在后端有SugarCRM.无法使用小提琴引起的关系，并且字段太乱了.

Is there something wrong with query ?? I have SugarCRM in backend. Cant use fiddle cause relation and fields are too messy.

推荐答案

此查询应该可以为您提供帮助.它做的最大的事情是计算所有约会的总数，然后将IF状态的SUM =完成，以在同一查询中获得总数和完成数.

This query should be able to help you. The biggest thing it does is count ALL of the appointments for total and then SUM on an IF status = completed to get you both the total and the completed in the same query.

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表.

Also, I was noticing you said that it was a One to Many relationship. Relational tables like you have are really for Many to Many. The most efficient way to have a One to Many is to get rid of the com_event_schedules_com_appointment_c table and add a com_event_schedule_id to the com_appointments table.

这篇关于MySQL查询1个查询中两个不同条件的不同计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！