mysql - 合并连接并存在于连接表上

Question

我一直在解决这个 sql 难题，我几乎想通了，但最后一部分有点挑战性。

我有一个事件表和一个用户表

事件的状态为:事件/非事件。

最重要的是，事件和用户之间有一个连接表:campaign_users。此表用于专门将事件分配给一部分用户。

鉴于此数据:

Campaigns
id  name        status
1   campaign_a  active
2   campaign_b  active
3   campaign_c  active
4   campaign_d  inactive

Users
id  name
1   user_a
2   user_b
3   user_c

Campaign_users
id  campaign_id     user_id
1   2               1
2   3               2
3   4               1

我想进行一个结合了几个“方面”的查询。正如我所说，我停留在最后一部分，这是我的步骤:

第 1 步:选择有效的事件:

SELECT campaigns.* 
FROM campaigns 
WHERE campaigns.status = 'active'

output
campaign: 1,2,3

第 2 步:(和)不专门分配给任何人:

SELECT campaigns.* 
FROM campaigns 
WHERE campaigns.status = 'active'
WHERE NOT EXISTS(select * FROM campaign_users WHERE campaign_users.campaign_id = campaigns.id)

output:
campaign: 1

第 3 步:(和)专门分配给 passed_in user_id (1)。

SELECT campaigns.* 
FROM campaigns 
WHERE campaigns.status = 'active' 
INNER JOIN campaign_users ON campaign_users.campaign_id = campaigns.id 
AND campaign_users.user_id = 1

output:
campaign: 2

最后一步:

这是我卡住的部分。

查询将所有“方面”组合在一起:仅不独占(未分配给任何人)且独占传入的 user_id 的事件广告系列。

写这个感觉有点矛盾，但内部连接只返回连接表中存在的事件，但它会跳过“非排他”事件。

expected output:

with passed_in user_id of 1

campaign: 1,2 (Active & non exclusive and exclusively assigned to user 2)

with passed_in user_id of 2

campaign: 1,3 (Active & non exclusive and exclusively assigned to user 2)

with passed_in user_id of 3

campaign: 3 (Active & non exclusive but no exclusive assigned to user 3)

Answer 1

您可以使用一个简单的技巧:将所有 campaign_users 外部连接到 campaigns。对于独家广告系列，您将获得针对每个广告系列和用户的一行，对于非独家广告系列，您将获得一行用户为空的行。为相关用户(比如用户 1)或所有用户(用户 null)保留所有事件。

select c.*
from campaigns c
left join campaign_users cu on cu.campaign_id = c.id
where c. status = 'active' and (cu.user_id = 1 or cu.user_id is null);