MySQL左联接与交叉联接查询问题 [英] MySQL Left join with cross join query issue

查看:165
本文介绍了MySQL左联接与交叉联接查询问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有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个新列AppointmentTypeIDAppointemntStatusAvailableInMarket.
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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆