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

让我解释一下。我在 Users 表中有公司的不同用户；公司在表 Site 中的不同位置，最后是表 Contact，将表用户与表 Sites 相关联。

这是我的查询:

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

我希望所有信息都在同一行，而不是 2 行几乎相同的行。我想要这样的东西:

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 ID 放在同一行，它应该可以工作。问题是因为它连接到两行，并连接到拆分行。因此，如果您两次加入联系人表(使用 is_director 和 is_assistant 的第二个子句)，您应该只有 1 个结果。然后加入您各自的 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？我假设它们是数字，所以您可能想确保您是这样对待它们的。