mysql不同的查询条件_MySQL查询1个查询中两个不同条件的不同计数

I have 3 tables named

com_event_schedules

com_appointments

com_event_schedules_com_appointment_c

which has a relation between first two tables.

Following are the fields of the tables

com_event_schedules

-- id

-- name

-- schedule_date

-- start_time

-- end_time

-- deleted

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)

relation between tables com_event_schedule and com_appointments is 1 to Many

What I want result having schedule_id, and total counts of its appointments on condition status='completed'

I tried following query:

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

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.

Is there something wrong with query ??

I have SugarCRM in backend. Cant use fiddle cause relation and fields are too messy.

解决方案

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

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.