在MySQL查询中未获得正确的COUNT()(已修改过的) [英] Not getting correct COUNT() in MySQL Query (Modified Earlier )

问题描述

我有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-开始时间-结束时间-已删除
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之间的关系是1对多

relation between tables com_event_schedule and com_appointments is 1 to Many

在结果schedule_id中我想要什么，以及状态为已完成"的约会的总人数

What I want in result schedule_id, and total counts of its appointments who's status='completed'

我尝试了以下查询:

SELECT sch.id,COUNT(app.status) 
FROM 
com_event_schedules sch, 
com_appointment app, 
com_event_schedules_com_appointment_c rel 
WHERE 
sch.id=com_event_schedules_com_appointmentcom_event_schedules_ida AND 
app.id=rel.com_event_schedules_com_appointmentcom_appointment_idb AND
app.status='completed'

我期望的结果是每个计划的ID，其中COUNT个约会的状态为已完成"，如果不匹配，则COUNT应该为"0".

What I'm expecting in result is id of each schedule with its COUNT of appointments which are having status "completed" and COUNT should be "0" if there is no match.

但是我得到的结果是只有约会身份已完成且状态为COUNT的计划ID.

But What Im getting in result is Only schedule id who is having appointment with status completed and COUNT 1.

例如

我有2个时间表，每个约会有3个约会.在第一个日程表的一个约会中，状态为已完成"

I'm having 2 schedule and for each I'm having 3 appointments. Out of which 1st schedule's one appointment is having status='completed'

所以我需要这样的结果

sch_id     app.status
1              1
2              0

推荐答案

尝试一下:

SELECT 
    sch.id,
    SUM(IF(app.status = 'completed' , 1, 0)) 
FROM 
    com_event_schedules sch
INNER JOIN  com_event_schedules_com_appointment_c rel 
    ON rel.com_event_schedules_com_appointmentcom_event_schedules_ida = sch.id 
INNER JOIN com_appointment app
    ON rel.com_event_schedules_com_appointmentcom_appointment_idb = app.id 
GROUP BY
    sch.id
HAVING
    COUNT(*) > 1

这篇关于在MySQL查询中未获得正确的COUNT()(已修改过的)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！