Сложное соединение с вложенным предложением Group-by / Have?

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

Вот что я сейчас использую:

select i.id, i.created_at 
from imports i 
where i.id in (
    select a.import_id 
    from albums a inner join songs s on a.id = s.album_id
    group by a.id having 1 = count(s.id)
);

Вложенный выбор (с объединением) работает очень быстро, но внешнее предложение in работает мучительно медленно.

Я попытался сделать весь запрос одним (без вложенности) соединением, но столкнулся с проблемами с предложениями group / Have. Лучшее, что я мог сделать, это список «импортных» записей с дубликатами, что неприемлемо.

Есть ли более элегантный способ составить этот запрос?


sql join group-by having
person Teflon Ted    schedule 23.02.2009    source источник
comment
Вы бы указали РСУБД?   -  person dance2die    schedule 24.02.2009

Ответы (5)


arrow_upward
8
arrow_downward

Как это?

SELECT i.id,
       i.created_at
FROM   imports i
       INNER JOIN (SELECT   a.import_id
                   FROM     albums a
                            INNER JOIN songs s
                              ON a.id = s.album_id
                   GROUP BY a.id
                   HAVING   Count(* ) = 1) AS TEMP
         ON i.id = TEMP.import_id;

В большинстве систем баз данных JOIN работает быстрее, чем WHERE ... IN.

person achinda99    schedule 23.02.2009
comment
Это было достаточно близко. Мне пришлось добавить группу по i.id, i.created_at, чтобы выполнить требование без дублирования (см. Исходный пост). Спасибо. - person Teflon Ted; 24.02.2009
comment
да, я пропустил это. нет проблем. - person achinda99; 24.02.2009

arrow_upward
4
arrow_downward 

SELECT i.id, i.created_at, COUNT(s.album_id)
FROM imports AS i
    INNER JOIN albums AS a
        ON i.id = a.import_id
    INNER JOIN songs AS s
        ON a.id = s.album_id
GROUP BY i.id, i.created_at
HAVING COUNT(s.album_id) = 1

(Возможно, вам не нужно включать COUNT в сам список SELECT. SQL Server этого не требует, но возможно, что это может сделать другая СУБД.)

person LukeH    schedule 23.02.2009

arrow_upward
2
arrow_downward

Не проверено:

select
    i.id, i.created_at
from
    imports i
where
    exists (select *
       from
           albums a
           join
           songs s on a.id = s.album_id
       where
           a.import_id = i.id
       group by
           a.id
       having
           count(*) = 1)

OR

select
    i.id, i.created_at
from
    imports i
where
    exists (select *
       from
           albums a
           join
           songs s on a.id = s.album_id
       group by
           a.import_id, a.id
       having
           count(*) = 1 AND a.import_id = i.id)
person gbn    schedule 23.02.2009

arrow_upward
1
arrow_downward

Все три предложенных метода должны быть быстрее, чем ваш WHERE IN:

  1. Существует со связанным подзапросом (gbn)
  2. Подзапрос, связанный с внутренним соединением (achinda99)
  3. Внутреннее объединение всех трех таблиц (Люк)

(Все тоже должны работать ... так что +1 для всех. Сообщите нам, если один из них не работает!)

Какой из них окажется самым быстрым, зависит от ваших данных и плана выполнения. Но это интересный пример различных способов выражения одного и того же в SQL.

person Thorsten    schedule 23.02.2009

arrow_upward
1
arrow_downward

Я попытался сделать весь запрос одним (без вложенности) соединением, но столкнулся с проблемами с предложениями group / Have.

Вы можете присоединиться к подзапросу с помощью CTE (Common Table Expression), если вы используете SQL Server версии 2005/2008.

Насколько мне известно, CTE - это просто выражение, которое работает как виртуальное представление, которое работает только с одним-единственным оператором select. Таким образом, вы сможете сделать следующее. Я обычно нахожу использование CTE для повышения производительности запросов.

with AlbumSongs as (
    select  a.import_id 
    from    albums a inner join songs s on a.id = s.album_id
    group by a.id 
    having 1 = count(s.id)
)
select  i.id, i.created_at 
from    imports i 
        inner join AlbumSongs A on A.import_id = i.import_id
person dance2die    schedule 23.02.2009