代码之家 › 专栏 › 技术社区 › Iason

当在其他表中找到匹配项时,我如何设置具有布尔值的自定义列?

select mysql

Iason · 技术社区 · 5 年前

嗨,当在不同的表中找到匹配项时,我正在努力创建一个自定义列。

表结构:

表联系人包含表列

身份证件
其他一些专栏对我的问题并不重要

表项目包含表列

身份证件
名称

表匹配包含表列

身份证件
project_id(等于项目表id)
contact_id(等于联系人表id)

问题是

例如,我想检索在项目1和2上匹配的联系人。使用左连接,我将根据与匹配表中的“contact_id”匹配的联系人“id”连接联系人信息。现在我想给这个联系人添加一列,说明该联系人是项目1或/和2的一部分

因此,如果存在联系人,则输出应该是与项目id为1和/或项目id为2的表匹配。

表格应该如下所示:

联系方式 id(联系默认列,对我的问题不重要)、project1、project2等

123,(一些默认列),1,0(例如)

我不希望的是,在输出表中,同一联系人多次出现,只是在project1列或project2列中设置了不同的值。因此,联系人需要以某种方式进行分组! 此外,不应绑定可以作为列显示的项目数量,因为一个联系人可以连接到10个项目。

我想到的东西太棒了。

SELECT contacts.*, (fields project1 and project2, etc. more columns when there are more matches)  
FROM contacts 
 LEFT JOIN matches on contacts.id = matches.contact_id
 LEFT JOIN projects ON projects.project_id = matches.project_id
GROUP BY contacts.id

有人能帮我解答这个问题吗。因为在这一点上,我不知道如何解决这个问题,所以我尝试了多种方法,但都没有得到上述结果。

如果需要创建更多的左连接,那么我可以实现它。

新增

因此,我设法做到了这一点,但是这个查询只显示了值为true的project1,但在这种情况下,project1和project2应该为true,但事实并非如此,因为它是在contact.id上分组的,这导致只显示匹配表中的第一个找到的记录,在本例中,该表是id为1的联系人和项目id为1之间的连接。

SELECT contacts.* , 
 IF(matches.project_id = 1, true,false ) as project1, 
 IF(matches.project_id = 2, true,false ) as project2
FROM contacts
 LEFT JOIN matches ON matches.contact_id = contacts.id
 LEFT JOIN projects ON projects.id = matches.project_id
GROUP BY contacts.id

当我离开该组时,它会显示两个正确的结果第一条记录显示project1为真,第二条记录显示project 2为真。现在我只需要一行中的project1和project2的值,因为联系人是相同的。

提前感谢。

0 回复 | 直到 5 年前

forpas 5 年前

而不是那些 IF s使用条件聚合:

SELECT contacts.*, 
  MAX(matches.project_id = 1) as project1, 
  MAX(matches.project_id = 2) as project2
FROM contacts
 LEFT JOIN matches ON matches.contact_id = contacts.id
 LEFT JOIN projects ON projects.id = matches.project_id
GROUP BY contacts.id

GMB 5 年前

您可以执行条件聚合:

select 
    c.id,
    max(m,project_id = 1) has_project_1, 
    max(m,project_id = 2) has_project_2
from contacts c
left join matches m 
    on m.contact_id = c.id
    and m.project_id in (1, 2)
group by c.id

笔记:

你不需要带 projects 表以获得您想要的结果。
您可以预先筛选您感兴趣的项目的联接,以限制要聚合的行数。
表别名使查询更易于编写、读取和维护。

但实际上,两个左连接可能更有效:

select
    c.*,
    (m1.project_id is not null) hzs_project_1,
    (m2.project_id is not null) hzs_project_2
from contact c
left join matches m1
    on m1.contact_id = c.id
    and m1.project_id = 1
left join matches m3
    on m2.contact_id = c.id
    and m2.project_id = 2