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

本文介绍了SQL多对多表的联接+逗号分隔的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这些表:

媒体表– 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.