希望帮助构建MySQL复杂查询 - Wishing for help to build MySQL complex query

我有 3 个 MySQL 表，例如 -

**tblCampaigns**
campId  campName    startDate            endDate              status
1       Campaign1   2016-03-10 00:00:00  2016-03-17 00:00:00  active    
2       Campaign2   2016-03-11 00:00:00  2016-03-18 00:00:00  active
**tblServices**
serviceId   serviceName
1           Car Washing
2           Car Painting
**tblCampaignServices**
id  campId  serviceId
1   1       1
2   1       2

我已经提供了通过自动完成选择向广告系列添加多个服务的选项。我的要求是，当用户键入自动完成以选择要添加到广告系列的服务时，只有那些尚未添加到开始日期和结束日期在当前广告系列开始和结束日期之间的其他广告系列中的 serviceName 才应出现在建议列表中。我的意思是日期不应与其他活动日期相交。此外，该服务名称不应已添加到同一广告系列中。

我做了半简单的查询，喜欢获取尚未在同一广告系列中添加的服务 -

select    tblServices.serviceId,tblServices.serviceName 
from      tblServices 
LEFT JOIN tblCampaignServices ON (tblServices.serviceId=tblCampaignServices.serviceId 
AND       tblCampaignServices.campaignId=2) 
where     ISNULL(tblCampaignServices.id)

首先，是否可以进行单个查询来完全满足我的要求。如果是，请帮助构建它。如果否，哪个是最好的方法？

以下查询是您要查找的内容。

SELECT DISTINCT    
tblServices.serviceId,
tblServices.serviceName
FROM tblServices 
LEFT JOIN tblCampaignServices ON tblServices.serviceId=tblCampaignServices.serviceId
LEFT JOIN tblCampaigns ON tblCampaigns.campId = tblCampaignServices.campId
WHERE
tblCampaigns.campId IS NULL
OR(
(endDate <
(SELECT startDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)
OR 
startDate >
(SELECT endDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2))
AND serviceName NOT IN
(SELECT serviceName
FROM tblCampaignServices 
LEFT JOIN tblServices ON tblServices.serviceId=tblCampaignServices.serviceId
LEFT JOIN tblCampaigns ON tblCampaigns.campId = tblCampaignServices.campId
WHERE
(startDate BETWEEN
(SELECT startDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)
AND
(SELECT endDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2))
OR
(endDate BETWEEN
(SELECT startDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)
AND
(SELECT endDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)))
AND
tblCampaigns.campId <> 2);

它

非常复杂，所以让我们看看它是如何工作的：

首先，我们加入所有信息，因为我们还需要开始和结束日期来选择允许的服务

我们选择不在广告系列中的所有服务：

tblCampaigns.campId IS NULL

我们选择广告系列中与当前服务不重叠的所有服务，即结束日期低于当前服务的开始日期，或者开始日期大于当前服务的结束日期：

(endDate <
(SELECT
startDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)
OR 
startDate >
(SELECT
endDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)

我们会从结果集中排除那些与当前广告系列重叠的服务，即当前广告系列的开始日期和结束日期之间的开始日期或结束日期。

serviceName NOT IN
(SELECT serviceName
FROM tblCampaignServices 
LEFT JOIN tblServices ON tblServices.serviceId=tblCampaignServices.serviceId
LEFT JOIN tblCampaigns ON tblCampaigns.campId = tblCampaignServices.campId
WHERE
(startDate BETWEEN
(SELECT startDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)
AND
(SELECT
endDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2))
OR
(endDate BETWEEN
(SELECT startDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)
AND
(SELECT endDate
FROM tblCampaigns
WHERE tblCampaigns.campId=2)))

我们排除了当前广告系列中已有的服务：

tblCampaigns.campId <> 2

由于结果集将具有重复的服务，并且由于多个不重叠的广告系列中存在服务，因此我们将 DISTINCT 添加到顶部的 SELECT 以避免重复服务。

如果当前广告系列的开始日期和结束日期与现有广告系列相同，则查询也有效。

测试台

 tblCampaigns
 campId campName    startDate           endDate             status 
 1      Campaign1   2016-03-10 00:00:00 2016-03-17 00:00:00 active  
 2      Campaign2   2016-03-11 00:00:00 2016-03-18 00:00:00 active  
 3      Campaign3   2016-02-11 00:00:00 2016-02-18 00:00:00 closed  
 4      Campaign4   2016-04-11 00:00:00 2016-04-18 00:00:00 active  
 tblServices
 serviceId   serviceName
 1           Car Washing
 2           Car Painting
 3           Car Maintenance
 4           Brake Replacement
 5           Gear Replacement
 tblCampaignServices
 id campId  serviceId
 1     1       1
 2     1       2
 3     3       1
 4     3       2
 5     3       3
 6     2       4
 7     4       3

使用此测试表，查询的结果为：

serviceId   serviceName
    3       Car Maintenance
    5       Gear Replacement

满足所有要求。

问候