sql合并两个子查询

Question

我有两张桌子。表A 有一个id 列。表B 有一个Aid 列和一个type 列。示例数据：

A:  id
    --
    1
    2

B: Aid | type
   ----+-----
   1   |  1
   1   |  1
   1   |  3
   1   |  1
   1   |  4
   1   |  5
   1   |  4
   2   |  2
   2   |  4
   2   |  3

我想从表 A 中获取所有包含一定数量的类型 1 和类型 3 操作的 ID。我的查询如下所示：

SELECT id 
FROM A
WHERE (SELECT COUNT(type)
       FROM B
       WHERE B.Aid = A.id
         AND B.type = 1) = 3 
  AND (SELECT COUNT(type)
       FROM B
       WHERE B.Aid = A.id
         AND B.type = 3) = 1

因此对于上面的数据，应仅返回 id 1 。

我可以以某种方式组合这两个子查询吗？目标是使查询运行得更快。

Answer 1

postgres 支持 CTE 吗？

WITH counts (Counts, Type, Aid) as (
  select count(type), type
  from b group by Type, Aid
)
  select id
  from A
  join Counts B1 on b1.Aid = a.id and b1.type = 1
  join Counts B3 on b3.Aid = a.id and b3.type = 3
where
  b1.counts = 3 and b3.counts = 1

我建议比较执行计划，但我怀疑它会相似，因为所有内容都应该在执行之前崩溃。

Answer 2

Select ...
From A
    Join    (
            Select B.Id
                , Sum ( Case When B.Type = 1 Then 1 Else 0 End ) As Type1Count
                , Sum ( Case When B.Type = 3 Then 1 Else 0 End ) As Type3Count
            From B
            Where B.Type In(1,3)
            Group By B.Id
            ) As Z
        On Z.Id = A.Id
Where Z.Type1Count = 3
    And Z.Type3Count = 1

Answer 3

这在 TSQL 中有效，在 Postgres 中也有效吗？

SELECT A.ID
FROM A
WHERE A.ID in
(
SELECT AID
FROM B
GROUP BY AID
HAVING
  SUM(CASE WHEN Type = 1 THEN 1 ELSE 0 END) = 3
  OR SUM(CASE WHEN Type = 3 THEN 1 ELSE 0 END) = 1
)

Answer 4

另一种选择：

SELECT DISTINCT Aid FROM (
   SELECT Aid,type,count(*) as n from B 
   GROUP BY Aid,type, ) as g 
 WHERE ( g.n=1 AND  g.type =  3 ) 
   OR  ( g.n=3 AND  g.type =  1 ) 

不过，我怀疑这会比你原来的表现更好。
您似乎正在采取最好的策略：仅计算候选行。
也许一些多余的预过滤可能会有所帮助：

SELECT DISTINCT Aid FROM (
   SELECT Aid,type,count(*) as n from B
   WHERE g.type =  3 OR g.type =  1  -- prefilter 
   GROUP BY Aid,type, ) as g 
 WHERE ( g.n=1 AND  g.type =  3 ) 
   OR  ( g.n=3 AND  g.type =  1 )