SQL多对多表的联接+逗号分隔 [英] SQL multiple join on many to many tables + comma separation

问题描述

我有这些表:

媒体表– id int主键，uri varchar.
media_to_people – media_id int主键，people_id int主键
people – id int主键，名称varchar，role int-role指定相对于媒体，此人是否是艺术家，发行者，作家，演员等，范围是(1-10)

media table – id int primary key, uri varchar.
media_to_people – media_id int primary key, people_id int primary key
people – id int primary key, name varchar, role int -- role specifies whether the person is an artist, publisher, writer, actor, etc relative to the media and has range(1-10)

这是多对多关系

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

I want to fetch a media and all its associated people in a select. So if a media has 10 people associated with it, all 10 must come.

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

Further more, if multiple people with the same role exist for a given media, they must come as comma separated values under a column for that role.

结果标题必须类似于:media.id，media.uri，people.name(演员)，people.name(艺术家)，people.name(发布者)等等.

Result headings must look like: media.id, media.uri, people.name(actor), people.name(artist), people.name(publisher) and so on.

我正在使用sqlite.

I'm using sqlite.

推荐答案

我同意Alex Martelli的

I agree with Alex Martelli's answer, that you should get the data in multiple rows and do some processing in your application.

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

If you try to do this with just joins, you need to join to the people table for each role type, and if there are multiple people in each role, your query will have Cartesian products between these roles.

因此，您需要使用 GROUP_CONCAT() 进行此操作，并在您的代码中生成标量子查询每个角色的选择列表:

So you need to do this with GROUP_CONCAT() and produce a scalar subquery in your select-list for each role:

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;

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

This is truly ugly! Don't try this at home!

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

Take our advice, and don't try to format the pivot table using only SQL.

这篇关于SQL多对多表的联接+逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！