如何从不同的行中获取唯一值

I have a database with rows having actors name in it example

我有一个数据库，其中包含具有actor名称的行示例

row1       row2
movie1     actor1,actor2,actor3
movie2     actor2
movie3     actor1,actor3,actor4,actor6

now i want to display a list of actors in one page with no repeat actor in it .. is it possible ... output should be

现在我想在一个页面中显示一个演员列表，其中没有重复演员..是否可能......输出应该是

actor1
actor2(no repeate)
actor3
actor4

here wt was i trying

这里wt是我尝试

function actors(){
    global $host, $dbname, $user, $pass;
    $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $STH = $DBH->prepare("SELECT DISTINCT actors FROM movies");

    $STH->execute();
    $STH->setFetchMode(PDO::FETCH_ASSOC);
    return $STH;
    }

$STH = actors();
     while (($row = $STH->fetch()) !== false) {
     echo $row['actors'].'</br>';
     }

but this dont work ... please help Edit I am getting output

但这不起作用...请帮助编辑我得到输出

actor1,actor2,actor3
actor2
actor1,actor3,actor4,actor6

2 个解决方案

#1

If you are keeping all actor names in a single field, i don't know a simple SQL query which can do this. It's probably not possible or should cost a lot of CPU.

如果要将所有actor名称保存在单个字段中，我不知道可以执行此操作的简单SQL查询。它可能不可能或者应该花费很多CPU。

You need to redesign your tables like below.

您需要重新设计表格，如下所示。

movies
------
id
name

actors
------
id
name

movie_actor_link
----------------
movie_id
actor_id

After doing this, it's already very simple to list actors since they have their own table with unique values.

执行此操作后，列出演员已经非常简单，因为他们拥有自己的具有唯一值的表。

#2

What you seek requires that you first split the actor values into rows and then use a DISTINCT query.

您寻求的内容要求您首先将actor值拆分为行，然后使用DISTINCT查询。

This is an example from Federico Cargnelutti

这是Federico Cargnelutti的一个例子

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Then our query might look like:

然后我们的查询可能如下所示：

Select Distinct actor
From (
     Select Split_Str( actor, ',', 1 ) As actor From Movies
     Union All Select Split_Str( actor, ',', 2 ) From Movies
     Union All Select Split_Str( actor, ',', 3 ) From Movies
     ...
     Union All Select Split_Str( actor, ',', n ) From Movies
     ) As Z

From

从

#1