时间戳

首页 > 解决方案 > 从唯一值表中查找出现次数

mysql - 从唯一值表中查找出现次数

问题描述

我有一个包含三个表的电影数据库。电影参与、人物和电影角色

我的问题是我必须找到每一个在 200 多部电影中扮演独特角色的演员。

一个独特的角色，是一个在电影角色表中只出现一次的角色。

我认为我通过以下查询找到了唯一字符表

SELECT filmcharacter.partid FROM filmcharacter GROUP BY 
filmcharacter.partid HAVING count(*) = 1;

我将其重写为 WITH 语句，形式为

WITH unique_table(partid) AS (SELECT partid FROM filmcharacter GROUP 
BY partid HAVING count(*) = 1)

但是我不确定如何进一步解决这个问题。我打算使用 WITH 来创建一个包含我需要的数据的表，然后选择我想要的行，将它们与相关的东西连接起来。

   WITH unique_table(partid) AS (SELECT partid FROM filmcharacter 
   INNER 
   JOIN filmparticipation ON(filmcharacter.partid = 
   filmparticipation.partid) INNER JOIN person(person.personid = 
   filmparticipation.personid) GROUP 
   BY partid)

我不确定接下来如何继续我尝试过

   WITH unique_table(partid) AS (SELECT partid FROM filmcharacter 
   INNER 
   JOIN filmparticipation ON(filmcharacter.partid = 
   filmparticipation.partid) INNER JOIN person(person.personid = 
   filmparticipation.personid) GROUP 
   BY partid) SELECT * FROM unique_table GROUP BY partid HAVING 
   count(partid) > 200;

但是如果发送它，我会得到查询零表。

这些表具有以下键：

Filmparticipation(personid, partid)

Filmcharacter(partid)

Person(personid)

我期望得到的是类似于表格的东西。它有一个标识演员 (personid) 的 ID，以及他们作为独特角色出现的次数

  Actor       Count
----------|----------
     1        201
     2        309

标签： mysqljoincount

解决方案

您可以加入表格person并filmparticipation使用not exists以确保在以下情况下partid不会再次发生相同的情况filmparticipation：

select p.personid, count(*) as `count`
from person p
inner join filmparticipation fp on fp.personid = p.personid
where not exists (
    select 1
    from filmparticipation fp1
    where fp1.partid = fp.partid and fp.personid != p.personid
)
group by p.personid
having count(*) > 200

如果您正在运行 MySQL 8.0，这也可以通过窗口计数来实现：

select personid, count(*)
from (
    select p.personid, count(*) over(partition by fp.partid) cnt
    from person p
    inner join filmparticipation fp on fp.personid = p.personid
) x
where cnt = 1
group by person_id
having count(*) > 200

推荐阅读