带有嵌套 group-by/having 子句的复杂连接？

Question

我最终需要一个包含“专辑”的“导入”记录列表 每张唱片只有一首“歌曲”。

这就是我现在使用的：

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/having 条款的问题。 我能做的最好的就是 带有欺骗行为的“导入”记录列表，这是不可接受的。

有没有更优雅的方式来编写这个查询？

Answer 1

这个怎么样？

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 的速度要快。

Answer 2

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

（您可能不需要在 SELECT 列表本身中包含 COUNT。SQL Server 不需要它，但不同的 RDBMS 可能需要它。）

Answer 3

未经测试：

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)

或

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)

Answer 4

所有三种建议的技术应该比您的 WHERE IN 更快：

1. 存在相关子查询 (gbn)
2. 内部联接的子查询 (achinda99)
3. 内部联接所有三个表 (luke)

（所有这些都应该有效...，所以 +1所有这些。如果其中之一不起作用，请告诉我们！）

哪一个实际上是最快的，取决于您的数据和执行计划。 这是一个用 SQL 表达同一事物的不同方式的有趣示例。

Answer 5

我试图将整个查询变成
单个（无嵌套）连接但遇到
团体的问题/有
条款。

如果您使用的是 SQL Server 版本 2005/2008，则可以使用 CTE（公共表表达式）加入子查询

据我所知，CTE 只是一种表达式，其工作方式类似于虚拟视图，只能在单个选择中工作 声明 - 因此您将能够执行以下操作。
我通常发现使用 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