英文:
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 <> 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 ', ') 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论