MySQL LEFT OUTER JOIN有一些最终结果问题 [英] MySQL LEFT OUTER JOIN is having some final results issue

问题描述

我有2张桌子
表1:tbl_appointments
表2:tbl_appointmentschedule_details

I have 2 tables
Table 1 : tbl_appointments
Table 2: tbl_appointmentschedule_details

Table1

AppointmentTypeID | AppointmentTimeID | AppointmentDate | NumberOfApplicants
-----------------------------------------------------------------------------
11                          23             10-16-2015           1
11                          23             10-16-2015           1
11                          24             10-16-2015           1
11                          24             10-16-2015           1
11                          23             10-16-2015           1
11                          24             10-16-2015           1
11                          25             10-16-2015           1
11                          22             10-17-2015           1
11                          22             10-17-2015           1
11                          22             10-17-2015           1
11                          22             10-17-2015           1

Table2

ScheduleID | AppointmentTimeID 
----------------------------
27              22
27              23
27              24
27              25
27              26

1. 我希望我所有来自Table2的AppointmentTimeID应该与table1匹配，并在最终结果中显示这些table2.AppointmentTimeID是否与table1.AppointmentTimeID匹配，但应该显示日期和零NumberOfApplicant.
2. 然后我希望它在table1.AppointmentDate中的'2015-10-15'和'2015-10-15'之间的日期之间使用.
3. 我的最终结果应该是每天来自table2的所有值以及table1.NumberOfApplicats的总和.
4. 请看看我的最终结果应该是这样

我正在使用以下查询

SELECT ad.AppointmentTimeID, COALESCE(sum(a.NumberOfApplicants),0) AS TBooked, a.AppointmentDate <br>FROM tbl_appointmentschedule_details ad
LEFT OUTER JOIN tbl_appointments a
ON ad.AppointmentTimeID = a.AppointmentTimeID
AND (a.AppointmentDate BETWEEN '2015-10-16' AND '2015-10-17')
AND ad.ScheduleID = 27
AND a.AppointmentTypeID = 11
WHERE a.AppointmentDate IS NOT NULL
GROUP BY a.AppointmentDate, ad.AppointmentTimeID
ORDER BY a.AppointmentDate ASC

它仅显示表1中与约会时间id匹配的那些记录，但我想查看所有记录.

It is showing only those records which is available in table1 matched with appointmenttimeid, but I want to see all the records.

FINAL RESULTS I WANT SHOULD BE
AppointmentTimeID | AppointmentDate  | NumberOfApplicants
---------------------------------------------------------
22                    16-10-2015          0
23                    16-10-2015          3
24                    16-10-2015          3
25                    16-10-2015          1
26                    16-10-2015          0

22                    17-10-2015          4
23                    17-10-2015          0
24                    17-10-2015          0
25                    17-10-2015          0
26                    17-10-2015          0

我的查询仅显示那些具有类似值的记录

My query is only showing those records which have values like that

MY QUERY OUTPUT
AppointmentTimeID | AppointmentDate  | NumberOfApplicants
---------------------------------------------------------
23                    16-10-2015          3
24                    16-10-2015          3
25                    16-10-2015          1
22                    17-10-2015          4

请帮助我解决此问题.

推荐答案

这应该为您做到:

select 
  m.AppointmentTimeID, 
  m.AppointmentDate, 
  coalesce(sum(ap.NumberOfApplicants),0) as NoOfApplicants

from (
  -- just basically doing a cross to get all time/date combinations
  select distinct d.AppointmentTimeID, a.AppointmentDate
  from tbl_appointments a, tbl_appointmentschedule_details d
) m

left join tbl_appointments ap 
  on ap.AppointmentTimeID = m.AppointmentTimeID
  and ap.AppointmentDate = m.AppointmentDate

where m.AppointmentDate between '2015-10-16' and '2015-10-17'

group by m.AppointmentDate, m.AppointmentTimeID
order by m.AppointmentDate, m.AppointmentTimeID

SQLFiddle示例: http://sqlfiddle.com/#!9/1e632/15

SQLFiddle example: http://sqlfiddle.com/#!9/1e632/15

Results:
| AppointmentTimeID |           AppointmentDate | NoOfApplicants |
|-------------------|---------------------------|----------------|
|                22 | October, 16 2015 00:00:00 |              0 |
|                23 | October, 16 2015 00:00:00 |              3 |
|                24 | October, 16 2015 00:00:00 |              3 |
|                25 | October, 16 2015 00:00:00 |              1 |
|                26 | October, 16 2015 00:00:00 |              0 |
|                22 | October, 17 2015 00:00:00 |              4 |
|                23 | October, 17 2015 00:00:00 |              0 |
|                24 | October, 17 2015 00:00:00 |              0 |
|                25 | October, 17 2015 00:00:00 |              0 |
|                26 | October, 17 2015 00:00:00 |              0 |

为了加快速度，您可以从一些索引中受益:

In order to speed things up a little, you might benefit from some indexing:

create index idx_tbl_appointments_apptdate_timeid on tbl_appointments(AppointmentDate, AppointmentTimeID);

create index idx_tbl_appointmentschedule_details_TimeID on tbl_appointmentschedule_details(AppointmentTimeID);

查询修改:

select 
  m.AppointmentTimeID, 
  m.AppointmentDate, 
  coalesce(sum(ap.NumberOfApplicants),0) as NoOfApplicants
from (
  select distinct AppointmentTimeID, AppointmentDate 
  from (select distinct AppointmentTimeID from tbl_appointmentschedule_details) one
  cross join
  (select distinct AppointmentDate from tbl_appointments
   where AppointmentDate between '2015-10-16' and '2015-10-17') two
) m
left join tbl_appointments ap 
  on ap.AppointmentTimeID = m.AppointmentTimeID
  and ap.AppointmentDate = m.AppointmentDate
where m.AppointmentDate between '2015-10-16' and '2015-10-17'
group by m.AppointmentDate, m.AppointmentTimeID
order by m.AppointmentDate, m.AppointmentTimeID

SQLFiddle示例: http://sqlfiddle.com/#!9/0de6d7/1

SQLFiddle example: http://sqlfiddle.com/#!9/0de6d7/1

请注意，我已在此查询的两个位置添加了日期跨度.查看此查询如何为您执行.

Notice that I have added date span in two locations in this query. See how this query performs for you.

这篇关于MySQL LEFT OUTER JOIN有一些最终结果问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！