如何在连接字段中使用逗号分隔列表连接两个表 [英] How to join two tables using a comma-separated-list in the join field

问题描述

我有两个表，类别和电影。

在电影表中我有一个列类别。

下面是一个例子：

 表类别{
 -id- -name- 
 1行动
 2喜剧
 4戏剧
 5舞蹈
} 
 
表电影{
 -id- -categories-（以及c的一些列）
 1 2,4 
 2 1,4 
 4 3,5 
} 
  

现在回到实际问题：执行从运动图表中排除类别列的查询，而是从类别表中选择匹配的类别并将其返回数组？像一个联接，但问题是有多个类别用逗号分隔，是否可以做一些正则表达式？

解决方案

在数据库字段中使用逗号分隔的列表是一种反模式，应尽可能避免。

因为它是一个PITA，用于在SQL中提取这些逗号分隔的值。

您应该添加一个单独的链接表来表示类别和电影之间的关系，例如：

 表类别
 id整数auto_increment主键
名称varchar（255）
 
表电影
 id整数auto_increment主键
名称varchar（255）
 
表movie_cat 
 movie_id整数外键引用movies.id 
 cat_id整数外键引用categories.id 
主键（movie_id， cat_id）
  

现在您可以执行

  SELECT m.name as movie_title，GROUP_CONCAT（c.name）AS categories FROM movies m 
 INNER JOIN movie_cat mc ON（mc.movi​​e_id = m.id）
 INNER JOIN类别c ON（c.id = mc.cat_id）
 GROUP BY m.id 
  

返回您的问题

使用您可以执行的数据交替使用

  SELECT m.name as movie_title 
，CONCAT（c1.name，if（c2.name IS NULL，''，'，'），ifnull（c2.name，''））电影m 
 LEFT JOIN类别c2 ON 
（replace（substring（substring_index（m.categories，'，'，2），
 length（substring_index（m.categories，'，'，2 -  1））+ 1），'，'，''）= c2.id）
 INNER JOIN类别c1 ON 
（replace（substring（substring_index（m.categories，'，'，1） ，
 length（substring_index（m.categories，'，'，1  -  1））+ 1），'，'，''）= c1.id）
  / pre> 
 
 
请注意，最后一个查询仅适用于每个电影有2个或更少类别。
 
I have two tables, categories and movies.

In movies table I have a column categories. That column consists of the categories that movie fits in. The categories are IDs separated by a comma.

Here's an example:
Table categories {
  -id-       -name-
  1          Action
  2          Comedy
  4          Drama
  5          Dance
}

Table movies {
  -id-       -categories-  (and some more columns ofc)
  1          2,4
  2          1,4
  4          3,5
}
Now to the actual question: Is it possible to perform a query that excludes the categories column from the movies table, and instead selects the matching categories from the categories table and returns them in an array? Like a join, but the problem is there are multiple categories separated by comma, is it possible to do some kind of regex?
解决方案
Using comma separated lists in a database field is an anti-pattern and should be avoided at all costs.

Because it is a PITA to extract those comma separated values out agian in SQL.  

Instead you should add a separate link table to represent the relationship between categories and movies, like so:
Table categories
  id integer auto_increment primary key
  name varchar(255)

Table movies
  id integer auto_increment primary key
  name varchar(255)

Table movie_cat
  movie_id integer foreign key references movies.id
  cat_id integer foreign key references categories.id
  primary key (movie_id, cat_id)
Now you can do
SELECT m.name as movie_title, GROUP_CONCAT(c.name) AS categories FROM movies m
INNER JOIN movie_cat mc ON (mc.movie_id = m.id)
INNER JOIN categories c ON (c.id = mc.cat_id)
GROUP BY m.id
Back to your question

Alternativly using your data you can do  
SELECT m.name as movie_title
  , CONCAT(c1.name, if(c2.name IS NULL,'',', '), ifnull(c2.name,'')) as categories 
FROM movies m
LEFT JOIN categories c2 ON 
 (replace(substring(substring_index(m.categories, ',', 2),
  length(substring_index(m.categories, ',', 2 - 1)) + 1), ',', '') = c2.id)
INNER JOIN categories c1 ON 
 (replace(substring(substring_index(m.categories, ',', 1), 
  length(substring_index(m.categories, ',', 1 - 1)) + 1), ',', '') = c1.id)
Note that the last query only works if there are 2 or fewer categories per movie.

                        
这篇关于如何在连接字段中使用逗号分隔列表连接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！