MySQL -单行中的多个值

Question

我有以下表格：

表用户：

id_user | name | job
--------|------|----
1       | John | Director
2       | Lila | Assistant
3       | Paul | Director
4       | Jude | Assistant

表址：

id_site | city 
--------|-------
110     | Paris 
111     | London 

表格联系人：

id_user | id_site | is_assistant | is_director
--------|---------|--------------|------------
1       | 110     | NULL         | 1
2       | 110     | 1            | NULL
3       | 111     | NULL         | 1
4       | 111     | 1            | NULL

让我解释一下。我在表用户中有公司的不同用户；在表站点中有公司的不同位置，最后有一个将表用户与表站点联系起来的表格联系人。

以下是我的疑问：

SELECT 
site.*,
`assistant`.`job` AS "Assistant Job",`assistant`.`name` AS "Assistant name", 
`directors`.`job` AS "Job director",`directors`.`name` AS "Director name"
FROM `site`
LEFT OUTER JOIN contact ON contact.id_site = site.id_site                   
LEFT OUTER JOIN `users` AS assistant ON `contact`.`id_user` = `assistant`.`id_user` AND `contact`.`is_assistant` = "1"
LEFT OUTER JOIN `users` AS directors ON `contact`.`id_user` = `directors`.`id_user` AND `contact`.`is_director` = "1"
ORDER BY site.id_site

这个查询很好，但问题是我的结果如下：

id_site | city   | Assistant Job | Assistant name | Job director | Director name
--------|--------|---------------|----------------|--------------|-------------- 
110     | Paris  | NULL          | NULL           | Director     | John
110     | Paris  | Assistant     | Lila           | NULL         | NULL
111     | London | NULL          | NULL           | Director     | Paul
111     | London | Assistant     | Jude           | NULL         | NULL

我希望拥有同一行的所有信息，而不是有两个几乎相同的行。我想要的是：

id_site | city   | Assistant Job | Assistant name | Job director | Director name
--------|--------|---------------|----------------|--------------|-------------- 
110     | Paris  | Assistant     | Lila           | Director     | John
111     | London | Assistant     | Jude           | Director     | Paul

希望我说的够清楚了。你能帮帮我吗？

非常感谢。

斯特菲

Answer 1

如果将两个contact it放在同一行中，则应该可以工作。问题在于，因为它要连接到两行，并连接到拆分的行。因此，如果您两次加入contact表( is_director和is_assistant的第二个子句)，那么每个表应该只有一个结果。然后加入到各自的users表中。

SELECT 
    site.*,
    `assistant`.`job` AS "Assistant Job",
    `assistant`.`name` AS "Assistant name", 
    `directors`.`job` AS "Job director",
    `directors`.`name` AS "Director name"
FROM 
    `site`
LEFT OUTER JOIN 
    `contact` AS ContactAssistant
    ON ContactAssistant.id_site = site.id_site 
    AND ContactAssistant.is_assistant = "1"   -- Add this clause here
LEFT OUTER JOIN 
    `contact` AS ContactDirector
    ON ContactDirector.id_site = site.id_site 
    AND ContactDirector.is_director = "1"     -- Add this clause here             
LEFT OUTER JOIN 
    `users` AS assistant 
    ON ContactAssistant.`id_user` = `assistant`.`id_user` 
    -- AND `contact`.`is_assistant` = "1" -- No longer needed
LEFT OUTER JOIN 
     `users` AS directors 
     ON ContactDirector.`id_user` = `directors`.`id_user` 
     -- AND `contact`.`is_director` = "1" -- No longer needed
ORDER BY site.id_site

不相关，您的is_assistant和is_director字段是(TINY)INT还是VARCHAR？我假设它们是数字的，所以你可能想确定你是这样对待它们的。