douhuang9886 2017-03-07 14:30
浏览 184
已采纳

如何使用LEFT JOIN连接多个mySQL表?

table projects

+----+--------+------------+
| id | title  | project_id |
+----+--------+------------+
|  1 | blue   |      12345 |
|  2 | red    |      67890 |
|  3 | yellow |      11111 |
|  4 | rosa   |      22222 |
+----+--------+------------+

table connect

+----+------------+-----------+
| id | project_id | people_id |
+----+------------+-----------+
|  1 |      12345 |         4 |
|  2 |      12345 |         3 |
|  3 |      12345 |         2 |
|  4 |      22222 |         2 |
+----+------------+-----------+

table people

+----+-----------+-----------+----------+
| id | firstname |   name    | position |
+----+-----------+-----------+----------+
|  1 | Diana     | Rose      | singer   |
|  2 | Al        | Capone    | singer   |
|  3 | Barbara   | Streisand | actor    |  
|  4 | Ben       | Harper    | musician |
+----+-----------+-----------+----------+

This is the result I wish:

+----+---------+--------+----------+----------+
| id | project | singer | musician |  actor   |
+----+---------+--------+----------+----------+
|  1 | blue    | Capone | Harper   | Sreisand |
|  4 | rosa    | Capone |          |          |
+----+---------+--------+----------+----------+

I tried to achieve the result like this:

<?php
$pdo = $db->query('
  SELECT *   
  FROM projects 
    LEFT JOIN connect ON projects.project_id=connect.project_id
    LEFT JOIN people ON connect.people_id=people.id;');

while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
    echo <td>$row['title']</td>;
    echo "<td>";
    if ($row['position']== "singer"){echo $row['name'];}
    echo "</td>";
    echo "<td>";
    if ($row['position']== "musician"){echo $row['name'];}
    echo "</td>";
    echo "<td>";
    if ($row['position']== "actor"){echo $row['name'];}
    echo "</td>";
}
?> 

But my result is:

+----+---------+--------+----------+----------+
| id | project | singer | musician |  actor   |
+----+---------+--------+----------+----------+
|  1 | blue    | Capone |          |          |
|  1 | blue    |        | Harper   |          |
|  1 | blue    |        |          | Sreisand |
|  4 | rosa    | Capone |          |          |
+----+---------+--------+----------+----------+
  • 写回答

1条回答 默认 最新

  • dqstti8945 2017-03-07 15:04
    关注

    Pure SQL solution:

    SELECT res.id, res.project, 
      GROUP_CONCAT(res.singer) as singer, 
      GROUP_CONCAT(res.musician) as musician, 
      GROUP_CONCAT(res.actor) as actor
    FROM (
      SELECT prj.id as id, prj.title as project, 
        IF(ppl.position = 'singer', ppl.name, null) as singer ,
        IF(ppl.position = 'musician', ppl.name, null) as musician,
        IF(ppl.position = 'actor', ppl.name, null) as actor
        FROM projects prj
          LEFT JOIN connect cnt ON prj.project_id=cnt.project_id
          LEFT JOIN people ppl ON cnt.people_id=ppl.id
    ) res
    GROUP BY 1
    HAVING singer IS NOT NULL OR musician IS NOT NULL OR actor IS NOT NULL
    ORDER BY 1
    

    If you use this query, the php loop can be as simple as

    while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
        echo "<td>{$row['project']}</td><td>{$row['singer']}</td><td>{$row['musician']}</td><td>{$row['actor']}</td>";
    }
    

    The query groups all the people by project.id and concatenates names. The sub-query sets names per position. Projects without any person are filtered out. The same result can be achieved more efficiently using inner join:

    SELECT res.id, res.project, 
      GROUP_CONCAT(res.singer) as singer, 
      GROUP_CONCAT(res.musician) as musician, 
      GROUP_CONCAT(res.actor) as actor
    FROM (
      SELECT prj.id as id, prj.title as project, 
        IF(ppl.position = 'singer', ppl.name, null) as singer ,
        IF(ppl.position = 'musician', ppl.name, null) as musician,
        IF(ppl.position = 'actor', ppl.name, null) as actor
        FROM projects prj
          INNER JOIN connect cnt ON prj.project_id=cnt.project_id
          INNER JOIN people ppl ON cnt.people_id=ppl.id
    ) res
    GROUP BY 1
    ORDER BY 1
    

    but OP asked about left join explicitly.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 爬取豆瓣电影相关处理
  • ¥15 手机淘宝抓清除消息接口
  • ¥15 C#无selenium
  • ¥15 LD衰减计算的结果过大
  • ¥15 用机器学习方法帮助保险公司预测哪些是欺诈行为
  • ¥15 计算300m以内的LD衰减
  • ¥15 数据爬取,python
  • ¥15 怎么看 cst中一个面的功率分布图,请说明详细步骤。类似下图
  • ¥15 为什么我的pycharm无法用pyqt6的QtWebEngine
  • ¥15 FOR循环语句显示查询超过300S错误怎么办