MySQL 左连接与交叉连接查询问题

Question

我有 2 个表，请查看链接和当前运行的查询 http://sqlfiddle.com/#!9/40d9d/2

我面临的问题
1. 执行时间较长
2. 我希望如果在日期范围日期“x”之间 tbl_appoitmens 表中没有信息，则很多显示结果为零但 tbl_appointmentschedule_details 中的所有 AppointmentTimeID。
3.我想验证这3个新列AppointmentTypeID , AppointemntStatus和AvailableInMarket来自<strong>tbl_appointments</strong> .
4.ScheduleID来自<strong>tbl_appointmentschedule_details</strong>

查询输出速度确实很重要，我们可能会一次选择 2 - 3 年的记录。

我想要显示的结果类似于

AppointmentTimeID   AppointmentDate     NoOfApplicants22                  2015-10-16              223                  2015-10-16              424                  2015-10-16              525                  2015-10-16              226                  2015-10-16              222                  2015-10-17              523                  2015-10-17              224                  2015-10-17              225                  2015-10-17              226                  2015-10-17              222                  2015-10-18              023                  2015-10-18              024                  2015-10-18              025                  2015-10-18              026                  2015-10-18              0

Answer 1

试试这个:

select t4.AppointmentTimeID, t4.AppointmentDate,ifnull(t5.NumberOfApplicants,0)
from
    (select distinct t2.AppointmentTimeID, t1.AppointmentDate
  from tbl_appointmentschedule_details t2 join (select t.AppointmentDate from (
SELECT adddate('2015-10-16', @rownum := @rownum + 1) as 'AppointmentDate' FROM tbl_appointments
JOIN (SELECT @rownum := -1) r
LIMIT 31
) t 
where t.AppointmentDate between '2015-10-16' and '2015-10-18') t1)  t4
left join 
    (SELECT t2.AppointmentTimeID,t1.AppointmentDate,sum(t1.NumberOfApplicants) as 'NumberOfApplicants'
    FROM tbl_appointmentschedule_details t2
    LEFT JOIN tbl_appointments t1 on t2.AppointmentTimeID=t1.AppointmentTimeID
    WHERE t1.AppointmentStatus='Pending' AND t1.AvailableInMarket=0
    GROUP BY t2.AppointmentTimeID,t1.AppointmentDate) t5 
on t4.AppointmentDate=t5.AppointmentDate and t4.AppointmentTimeID=t5.AppointmentTimeID
having t4.AppointmentTimeID!=0
order by t4.AppointmentDate,t4.AppointmentTimeID;