MySQL左联接与交叉联接查询问题 [英] MySQL Left join with cross join query issue
问题描述
我有2个表,请参阅链接和当前运行的查询 http://sqlfiddle.com/#!9/40d9d/2
I have 2 tables please see the link and current running query http://sqlfiddle.com/#!9/40d9d/2
问题我正面临
1.花很多时间执行
2.我想如果在tbl_appoitmens表中的日期范围"x"之间没有任何信息,那么多显示结果为零,但tbl_appointmentschedule_details中的所有AppointmentTimeID.
3.我想验证tbl_appointments
中的这3个新列AppointmentTypeID
,AppointemntStatus
和AvailableInMarket
.
4. ScheduleID
来自tbl_appointmentschedule_details
Issues I am facing
1. taking much time to execute
2. I want if in between date range date 'x' have no information in tbl_appoitmens table then much show the results with zero but all AppointmentTimeID in tbl_appointmentschedule_details.
3. I want to verify these 3 new columns AppointmentTypeID
, AppointemntStatus
and AvailableInMarket
from tbl_appointments
.
4. ScheduleID
from tbl_appointmentschedule_details
查询输出速度确实很重要,我们可能会一次选择类似2-3年的记录.
我想要显示的结果类似
Query output speed really does matter we might be selecting like 2 - 3 years records at once.
results I want show be something like
AppointmentTimeID AppointmentDate NoOfApplicants
22 2015-10-16 2
23 2015-10-16 4
24 2015-10-16 5
25 2015-10-16 2
26 2015-10-16 2
22 2015-10-17 5
23 2015-10-17 2
24 2015-10-17 2
25 2015-10-17 2
26 2015-10-17 2
22 2015-10-18 0
23 2015-10-18 0
24 2015-10-18 0
25 2015-10-18 0
26 2015-10-18 0
推荐答案
尝试一下:
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;
这篇关于MySQL左联接与交叉联接查询问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!