多对多表上的SQL多连接+逗号分隔

Question

我有这些表格：

媒体表- id int主键，uri varchar。

media_to_people - media_id整型主键，people_id整型主键

people - id int主键，name varchar，role int -- role指定此人是否为相对于媒体的艺术家、出版商、作家、演员等，范围为(1-10)

这是一个多对多的关系

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

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

结果标题必须类似于: media.id、media.uri、people.name(演员)、people.name(艺术家)、people.name(出版商)等等。

我使用的是sqlite。

Answer 1

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

如果您尝试仅使用连接来执行此操作，则需要为每个角色类型连接到people表，并且如果每个角色中有多个人，则查询将在这些角色之间使用笛卡尔乘积。

因此，您需要使用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格式化数据透视表。

Answer 2

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，其方式取决于您的客户端)；数据将以几行的形式从DB到达您的客户端代码，您的客户端代码可以很容易地将它们放入您想要的单列形式中。

对于我们来说，很难说出如何编写客户端部分，而不了解您作为客户端所使用的环境或语言。但在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’的转换方法，因此显然不可能完全匹配您的规范……但这是我的独创性所能达到的最接近的；-)。