Mysql SELECT 仅显示 1 个结果而不是 3 个表中的多个结果

Question

我尝试发出一个 SQL 请求，从多个表中获取所有数据。例如，当一个用户有更多的“执行”时，问题就开始了。但首先让我们看一下代码:

SELECT c.id, c.firstname, c.surname, c.email, c.process, c.search_work, c.note,
               MAX(CASE WHEN cl.languageID = 1 THEN cl.skill ELSE '-' END)AS 'en',
               MAX(CASE WHEN cl.languageID = 2 THEN cl.skill ELSE '-' END)AS 'ge',
               ce.enforcement       
        FROM candidates AS c
        LEFT JOIN candidates_language AS cl ON c.id = cl.candidates_id
        LEFT JOIN candidates_enforcement as ce on c.id = ce.candidates_id
        GROUP BY c.id, c.firstname, c.surname, c.email

正如您从这里看到的，我在多个表上使用候选人 ID 上的外键进行搜索。

为此目的，这里是 2 个表的样子:

candidates

        ------------------------------------------------------------------------
        |   id   |     firstname    |      surname       |        email        |
        ------------------------------------------------------------------------
        |   22   |      John        |        Doe         |    john@doe.com     |
        ------------------------------------------------------------------------
        |   23   |      Peter       |       Miller       |    doe@john.com     |
        ------------------------------------------------------------------------

candidates_enforcement

        --------------------------------------------------
        |   id   |   candidates_id  |    enforcement     |
        --------------------------------------------------
        |    1   |        22        |       Advocate     |
        --------------------------------------------------
        |    2   |        22        |      Programmer    |
        --------------------------------------------------
        |    3   |        23        |       IT Admin     |
        --------------------------------------------------

candidates_id = 来自候选人的外键。使用我上面的 SQL 请求结果应该是这样的:

---------------------------------------------------------------------------------
| id |     firstname    |      surname       |        email        | enforcement
---------------------------------------------------------------------------------
| 22 |      John        |        Doe         |    john@doe.com     | Advocate, Programmer |
---------------------------------------------------------------------------------
| 23 |      Peter       |       Miller       |    doe@john.com     | IT Admin  
---------------------------------------------------------------------------------

不幸的是，它总是只显示 1 个来自“执行”的结果。所以对于 id 为 22 的候选人，它是 Advocate 而不是 Advocate, Programmer有没有人可以帮助我找到解决此问题的方法？谢谢

附注FIDDLE 上的工作演示 http://sqlfiddle.com/#!9/25b1b/1

Answer 1

您可以像这样使用 GROUP_CONCAT:

SELECT
    candidates.id,
    candidates.firstname,
    candidates.surname,
    candidates.email,
    group_concat(DISTINCT candidates_enforcement.enforcement)
FROM
    candidates
    LEFT JOIN candidates_enforcement
        ON candidates.id = candidates_enforcement.candidates_id
GROUP BY
    candidates.id,
    candidates.firstname,
    candidates.surname,
    candidates.email

引用:

• GROUP_CONCAT(expr)