mysql - 如何为此编写选择查询

Question

我有两张这样的 table

job(id,title,location,...)

job_page(id,id_job,id_page,page_name)

例如，我想编写一个选择查询来填充以下列的表格:

+_______+________+__________+_______+
|Job Id | Titlte | Location | Page  |
+_______+________+__________+_______+
|1      | Hot job| Somewhere| Page1 |
|       |        |          | Page2 |
+_______+________+__________+_______+

如何为此编写选择查询？

我尝试过，但结果并不像我预期的那样:

SELECT jp.page_name,j.* FROM jobs j
LEFT JOIN job_pages jp ON jp.id_job = j.id
GROUP BY j.id;

Answer 1

您需要使用group_concat:

 SELECT j.id, j.title, j.location, group_concat(jp.page_name)
 FROM jobs j LEFT JOIN job_pages jp ON jp.id_job = j.id
 GROUP BY j.id, j.title, j.location;

在 MySQL 中，您可以缩写 GROUP BY:

 SELECT j.id, j.title, j.location, group_concat(jp.page_name)
 FROM jobs j LEFT JOIN job_pages jp ON jp.id_job = j.id
 GROUP BY j.id;

但这确实让像我这样的多数据库用户感到困扰。