一次从 3 个表中查询 MySQL

Question

我尝试一次从 3 个表中获取数据，表是:

• 候选人
• 语言
• candidates_language

其中每一个都代表我数据库中所有用户的一些数据。

顾名思义，第一个表包含一些常规数据，例如:名字、姓氏、电子邮件。

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

第二个只包含 languages_id 和 languageName

languages
    ---------------------------------
    | languageID |     languageName |
    ---------------------------------
    |      1     |      English     |
    ---------------------------------
    |      2     |      German      |
    ---------------------------------
    |      3     |      Spanish     |
    ---------------------------------

最后一个尝试将所有这些联系在一起:

    candidates_language 
   (foreign keys: candidates_id to table candidates,languageID to languages)
        -----------------------------------------------------------------
        |   id   |   candidates_id  |  languageID |        skill        |
        -----------------------------------------------------------------
        |   1    |        22        |       1     |        basic        |
        -----------------------------------------------------------------
        |   2    |        22        |       3     |        basic        |
        -----------------------------------------------------------------
        |   3    |        23        |       1     |        advance      |
        -----------------------------------------------------------------
        |   4    |        23        |       2     |        basic        |
        -----------------------------------------------------------------

我需要将这 3 个表的结果连接到某些特定语言，并制作如下内容:

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

我尝试用下面的代码连接到这一点:

SELECT
    candidates.firstname,
    candidates.surname,
    candidates_language.candidates_id,
    candidates_language.skill,
    languages.languageID
FROM
    candidates,
    candidates_language,
    languages

不幸的是，我离我想要的实际结果还很远。有人可以帮我解决这个问题吗？

Answer 1

如果语言数量是预定义，那么您可以使用条件聚合来获得所需的结果集:

SELECT c.id, c.firstname, c.surname, c.email,
       MAX(CASE WHEN cl.languageID = 1 THEN cl.skill ELSE '-' END) AS 'English',
       MAX(CASE WHEN cl.languageID = 2 THEN cl.skill ELSE '-' END) AS 'German',
       MAX(CASE WHEN cl.languageID = 3 THEN cl.skill ELSE '-' END) AS 'Spanish'
FROM candidates AS c
LEFT JOIN candidates_language AS cl ON c.id = cl.candidates_id
GROUP BY c.id, c.firstname, c.surname, c.email

Demo Here

否则你必须雇用dynamic sql .