SQL 与嵌套分组依据/具有子句的复杂连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/579175/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Complex join with nested group-by/having clause?
提问by Teflon Ted
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.
嵌套选择（带有连接）非常快，但外部“in”子句非常缓慢。
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.
我试图使整个查询成为单个（无嵌套）连接，但遇到了 group/sharing 子句的问题。我能做的最好的事情是一个带有欺骗的“导入”记录列表，这是不可接受的。
Is there a more elegant way to compose this query?
有没有更优雅的方式来编写这个查询？
回答by achinda99
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.
在大多数数据库系统中，JOIN 的工作速度比 WHERE ... IN 快。
回答by LukeH
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
(You might not need to include the COUNTin the SELECTlist itself. SQL Server doesn't require it, but it's possible that a different RDBMS might.)
（您可能不需要将 包括COUNT在SELECT列表本身中。SQL Server 不需要它，但不同的 RDBMS 可能需要。）
回答by gbn
Untested:
未经测试：
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)
回答by Thorsten
All three sugested techniques should be faster than your WHERE IN:
所有三种建议的技术都应该比您的 WHERE IN 更快：
- Exists with a related subquery (gbn)
- Subquery that is inner joined (achinda99)
- Inner Joining all three tables (luke)
- 与相关子查询 (gbn) 一起存在
- 内连接的子查询 (achinda99)
- 内部连接所有三个表 (luke)
(All should work, too ..., so +1 for all of them. Please let us know if one of them does not work!)
（所有这些也都应该有效......，所以对所有这些 +1。如果其中一个不起作用，请告诉我们！）
Which one actually turns out to be the fastest, depends on your data and the execution plan. But an interesting example of different ways for expressing the same thing in SQL.
哪个实际上是最快的，取决于您的数据和执行计划。但是在 SQL 中表达同一事物的不同方式的有趣示例。
回答by dance2die
I tried to make the entire query a single (no nesting) join but ran into problems with the group/having clauses.
我试图使整个查询成为单个（无嵌套）连接，但遇到了 group/sharing 子句的问题。
You can join subquery using CTE (Common Table Expression) if you are using SQL Server version 2005/2008
如果您使用的是 SQL Server 2005/2008 版，则可以使用 CTE（公用表表达式）加入子查询
As far as I know, CTE is simply an expression that works like a virtual view that works only one a single selectstatement - So you will be able to do the following. I usually find using CTE to improve query performance as well.
据我所知，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
