Подзапрос для подсчета элементов, а затем их группировка по полю основного запроса без дубликатов

Я хочу подсчитать, сколько раз «дочерний элемент» появляется в отношениях N..N, и сгруппировать результаты по полю «родителя».

Мне трудно подобрать точные слова, поэтому, скажем, у меня есть 3 таблицы: Фильм, Актер, Игра, где < em>Воспроизведение — это отношение между Фильм и Актер. Актер может сыграть в кино. Возможно, некоторые актеры есть в базе данных, но они никогда не играли ни в одном фильме из базы данных.

В таблице Movie у меня есть жанр.

Я хочу подсчитать количество актеров, сыгравших в каждом жанре, не считая одного и того же актера более одного раза в каждом жанре.

В настоящее время я использую DISTINCT для act_id для каждой пьесы, что означает, что актер, который теоретически играл несколько раз в одном и том же фильме, появится один раз... но это бессмысленный сценарий (потому что актер появится только один раз в фильме). в таблице Play), так что это бесполезно, и не то, что я хочу. Любая лучшая идея?

Я хотел бы сохранить все в одном запросе, потому что на самом деле я делаю другие подзапросы, чтобы получить другую статистику по жанрам.

Вот как выглядит мой запрос без других подзапросов:

SELECT
    movie.genre,

    SUM(
        SELECT COUNT(DISTINCT play.actor_id)
        FROM play
        WHERE play.movie_id = movie.id
    ) AS number_of_actors

    FROM movie

    GROUP BY movie.genre

В настоящее время, если актер сыграл в нескольких фильмах, он будет засчитан несколько раз.


mysql count distinct-values correlated-subquery
person Joey Quint    schedule 27.08.2019    source источник
comment
Может ли фильм существовать без актера? То есть может ли существовать фильм id без соответствующей строки в таблице play?   -  person Madhur Bhaiya    schedule 27.08.2019

Ответы (2)


arrow_upward
1
arrow_downward

Ваша проблема в том, что у вас нет возможности общаться отдельно между каждым из элементов, которые суммируются, поэтому вы в конечном итоге будете дублировать значения. Проще записать это как JOIN. Я использовал LEFT JOIN в случае, если у фильма нет записей в таблице play, и в этом случае COUNT будет равно 0.

SELECT m.genre
       COALESCE(COUNT(DISTINCT p.actor_id), 0) AS number_of_actors
FROM movie m
LEFT JOIN play p ON p.movie_id = m.id
GROUP BY m.genre
person Nick    schedule 27.08.2019
comment
Я принимаю этот ответ вместо ответа Мадхура Бхайи, потому что в нем будут перечислены все фильмы даже с нулевым счетом, но оба ответа работают, поскольку я не упомянул об этом. Спасибо! - person Joey Quint; 29.08.2019
comment
@JoeyQuint, не беспокойся. Я рад, что смог помочь. - person Nick; 30.08.2019

arrow_upward
1
arrow_downward

Просто выполните простую операцию INNER JOIN между различными таблицами в соответствии с их соответствующими отношениями и используйте COUNT(DISTINCT ..) для группы из genre, чтобы получить уникальных актеров для каждого жанра:

SELECT 
  m.genre, 
  COUNT(DISTINCT p.actor_id) AS number_of_actors 
FROM movie AS m 
JOIN play AS p ON p.movie_id = m.id 
GROUP BY m.genre
person Madhur Bhaiya    schedule 27.08.2019