在我的数据库中有两列,数据如下
StartDate Enddate
2015-10-01 2015-10-30
2015-10-15 2015-11-15
2015-09-15 2015-10-15
如果我用起始日期:2015-10-16结束日期:2015-10-20搜索以上所有结果,请帮助我
我的查询如下
Select * from campaign as c LEFT JOIN campaign_team as t ON c.campaign_id=t.campaign_id where t.user_id
='6' and
(campaign_sdate BETWEEN '2015-10-16' AND '2015-10-20' or campaign_edate BETWEEN '2015-10-16' AND '2015-10-20'
or ( campaign_sdate <= 2015-10-16 and campaign_edate >= 2015-10-20 ) );
提前感谢
您已经编写了相反的条件
or ( campaign_sdate >= 2015-10-16 and campaign_edate <= 2015-10-20 ) );
使用时请删除BETWEEN
。
最终查询,
Select * from campaign as c LEFT JOIN campaign_team as t ON c.campaign_id=t.campaign_id where t.user_id
='6' or ( campaign_sdate >= 2015-10-16 and campaign_edate <= 2015-10-20 ) );
我认为这应该能在中工作
Select * from campaign as c LEFT JOIN campaign_team as t ON c.campaign_id=t.campaign_id where t.user_id ='6' and campaign_sdate >= 2015-10-16 and campaign_edate <= 2015-10-20;
我翻转了大于号和小于号。
将和查询包装在括号内,以便查询将它们分别计算为OR部分:
Select *
from campaign as c LEFT JOIN campaign_team as t ON c.campaign_id=t.campaign_id where t.user_id ='6'
and (
(campaign_sdate BETWEEN '2015-10-16' AND '2015-10-20'
or campaign_edate BETWEEN '2015-10-16' AND '2015-10-20')
or ( campaign_sdate <= '2015-10-16' and campaign_edate >= '2015-10-20' ) );
这是一个SQL Fiddle显示我的查询
编辑:在最近两个日期条件前后发现缺少"。
如果第1个周期在第2个开始之后结束,而第1个开始在第2结束之前,则两个周期重叠:
Select *
from campaign as c
JOIN campaign_team as t
ON c.campaign_id=t.campaign_id
where t.user_id ='6'
and campaign_sdate <= '2015-10-20'
AND campaign_edate >= '2015-10-16'
我删除了LEFT JOIN,因为where t.user_id ='6'
无论如何都会将结果更改为内部联接