SQL多对多表的联接+逗号分隔

一尘不染

SQL多对多表的联接+逗号分隔

sql

我有这些表：

媒体表-id int主键uri varchar。
media_to_people ― media_id int主键，people_id int主键
people ― id int主键，名称varchar，role int-角色指定该人相对于媒体是否是艺术家，发行者，作家，演员等，并且具有范围（1-10）

这是多对多关系

我想选择一个媒体及其所有相关人员。因此，如果媒体有10个人与之相关联，那么所有10个人都必须来。

此外，如果给定媒体存在多个具有相同角色的人，则这些人必须以逗号分隔的值出现在该角色的列下。

结果标题必须类似于：media.id，media.uri，people.name（演员），people.name（艺术家），people.name（发布者）等等。

我正在使用sqlite。

阅读 169

2021-05-16

共1个答案

一尘不染

我同意Alex Martelli的回答，即您应该获取多行数据并在应用程序中进行一些处理。

如果尝试仅通过联接执行此操作，则需要针对每个角色类型联接到人员表，并且如果每个角色中都有多个人员，则查询将在这些角色之间具有笛卡尔积。

因此，您需要执行此操作，GROUP_CONCAT()并在每个角色的选择列表中生成一个标量子查询：

SELECT m.id, m.uri, 
 (SELECT GROUP_CONCAT(name) 
  FROM media_to_people JOIN people ON (people_id = id) 
  WHERE media_id = m.id AND role = 1) AS Actors,
 (SELECT GROUP_CONCAT(name) 
  FROM media_to_people JOIN people ON (people_id = id) 
  WHERE media_id = m.id AND role = 2) AS Artists,
 (SELECT GROUP_CONCAT(name) 
  FROM media_to_people JOIN people ON (people_id = id) 
  WHERE media_id = m.id AND role = 3) AS Publishers
FROM media m;

这真是丑陋！不要在家尝试！

请采纳我们的建议，不要尝试仅使用SQL格式化数据透视表。

2021-05-16