SQL多个连接多个表+逗号分隔

我有这些表:

media table - id int主键,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指定此人是相对于媒体的艺术家,出版商,作家,演员等,并且具有范围(1 -10)

这是一对多关系

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

此外,如果给定媒体存在多个具有相同角色的人员,则他们必须在该角色的列下以逗号分隔值的形式出现.

结果标题必须如下:media.id,media.uri,people.name(actor),people.name(artist),people.name(publisher)等.

我正在使用sqlite.

SQLite没有启动器所需的"数据透视"功能,而"逗号分隔值"部分肯定是一个演示问题,试图推入任何数据库层是荒谬的(也可能是不可行的),无论如何可能涉及SQL的方言 - 它绝对是您在客户端中所做工作的一部分,例如报告工具或编程语言.

使用SQL进行数据访问,并将演示文稿留给其他层.

如何获取数据

SELECT media.id, media.uri, people.name, people.role
FROM media
JOIN media_to_people ON (media.id = media_to_people.media_id)
JOIN people ON (media_to_people.people_id = people.id)
WHERE media.id = ?
ORDER BY people.role, people.name

(？是指示SQLite中的参数的一种方式,绑定到您正在寻找的特定媒体ID,取决于您的客户端); 数据将从数据库到您的客户端代码分成几行,您的客户端代码可以轻松地将它们放入您想要的单列形式.

我们很难说如何编写客户端部分的代码,而不了解您作为客户端使用的环境或语言.但在Python中例如:

def showit(dataset):
  by_role = collections.defaultdict(list)
  for mediaid, mediauri, name, role in dataset:
    by_role[role].append(name)
  headers = ['mediaid', 'mediauri']
  result = [mediaid, mediauri]
  for role in sorted(by_role):
    headers.append('people(%s)' % role)
    result.append(','.join(by_role[role]))
  return ' '.join(headers) + '\n' + ' '.join(result)

即使这不符合您的规范 - 您要求标题如'people(artist)',同时您还指定角色编码为int,并且无法提及从int到字符串'artist' ,所以显然不可能完全匹配你的规格......但它和我的聪明才智一样接近;-).