找到至少有2位相同导演的流派,然后打印共同的流派和导演ID?

huangapple go评论53阅读模式
英文:

Find the genres that have at least 2 same directors, then print common genres and director id?

问题

数据集如下:

g1 = {d1,d3,d5}
g2 = {d1,d3,d2}
g3 = {d4,d3,d5}

我有一个名为director_genre的表格,内容如下:

  director_id | genre       
   d1           g1
   d1           g2
   d2           g2
   d3           g1
   d3           g2
   d3           g3
   d4           g3
   d5           g1
   d5           g3

我想要的输出是:

  genre |  director_id       
   g1      d1,d3,d5
   g2      d1,d3
   g3      d3,d5

我考虑的查询是:

SELECT genre
FROM director_genre
GROUP BY genre;

之后,根据问题要求,我不会提供其他逻辑。请帮我完成这个任务。

英文:

The dataset is like this:

g1 = {d1,d3,d5}
g2 = {d1,d3,d2}
g3 = {d4,d3,d5}

I have the following table called director_genre:

      director_id | genre       
       d1           g1
       d1           g2
       d2           g2
       d3           g1
       d3           g2
       d3           g3
       d4           g3
       d5           g1
       d5           g3

The output I want is

      genre |  director_id       
       g1      d1,d3,d5
       g2      d1,d3
       g3      d3,d5

The query I thought of is

SELECT genre
FROM director_genre
GROUP BY genre;

After this, I am not to put the logic as asked in the question.
Please help me to get this done.

答案1

得分: 3

我建议使用以下查询:

SELECT d.genre, GROUP_CONCAT(director_id)
FROM director_genre d
WHERE EXISTS (SELECT *
              FROM director_genre d2
              WHERE d.director_id = d2.director_id AND d.genre <> d2.genre
)
GROUP BY d.genre;

嵌套的子查询将仅选择与至少两种类型相关联的导演记录(对于d中的每个记录,它会查看是否存在d2中具有相同director_id但不同genre的记录)。然后,它们将按genre分组。

英文:

I would suggest the following query:

SELECT d.genre, GROUP_CONCAT(director_id)
FROM director_genre d
WHERE EXISTS (SELECT *
              FROM director_genre d2
              WHERE d.director_id = d2.director_id AND d.genre &lt;&gt; d2.genre
)
GROUP BY d.genre;

The nested subquery will select only records of directors who are associated with at least 2 genres (for each record in d it looks whether there exists a record in d2 with the same director_id but different genre). Then they will be grouped by genre.

答案2

得分: 1

你想要一个(类型,导演)列表,其中导演还与另一种类型相关联。然后分组。可以这样做:

select genre, group_concat(director_id separator ', ') as directors
from dg
where exists (
  select *
  from dg as x
  where x.director_id = dg.director_id
  and x.genre <> dg.genre
)
group by genre
英文:

You want a list of (genre, director) where the director is also associated with another genre. Then group. This could be done like so:

select genre, group_concat(director_id separator &#39;, &#39;) as directors
from dg
where exists (
  select *
  from dg as x
  where x.director_id = dg.director_id
  and x.genre &lt;&gt; dg.genre
)
group by genre

huangapple
  • 本文由 发表于 2023年6月8日 19:40:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/76431495.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定