使用嵌套的group-by / having子句进行复杂连接？ [英] Complex join with nested group-by/having clause?

问题描述

我最终需要一个导入记录列表，其中包括专辑
记录，每个记录只有一首歌曲。

这就是我现在正在使用：

 选择i.id，i.created_at $ b $ from进口i 
 where i.id in（
）从唱片集中选择a.import_id 
 a.id = s.album_id 
组合的a内部连接歌曲1 = count（s.id） 
）; 
  

嵌套select（加入）的速度非常快，但外部
in 子句是极其缓慢的。

我试图让整个查询成为单一（无嵌套）连接，但将
带入组/子句的问题。我能做的最好的是
a列表中的导入记录，这是不可接受的。

解决方案

这是怎么回事？

  SELECT i.id，
 i.created_at 
 FROM imports i 
 INNER JOIN（SELECT a.import_id 
 FROM albums a 
 INNER JOIN歌曲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更快。 / p>

I ultimately need a list of "import" records that include "album" records which only have one "song" each.

This is what I'm using now:

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)
);

The nested select (with the join) is blazing fast, but the external "in" clause is excruciatingly slow.

I tried to make the entire query a single (no nesting) join but ran into problems with the group/having clauses. The best I could do was a list of "import" records with dupes, which is not acceptable.

Is there a more elegant way to compose this query?

解决方案

How's this?

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; 

In most database systems, the JOIN works a lost faster than doing a WHERE ... IN.

这篇关于使用嵌套的group-by / having子句进行复杂连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！