本文介绍了MySQL LEFT OUTER JOIN有一些最终结果问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有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
- 我希望我所有来自Table2的AppointmentTimeID应该与table1匹配，并在最终结果中显示这些table2.AppointmentTimeID是否与table1.AppointmentTimeID匹配，但应该显示日期和零NumberOfApplicant.
- 然后我希望它在table1.AppointmentDate中的'2015-10-15'和'2015-10-15'之间的日期之间使用.
- 我的最终结果应该是每天来自table2的所有值以及table1.NumberOfApplicats的总和.
- 请看看我的最终结果应该是这样
我正在使用以下查询
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有一些最终结果问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！