如何为子查询命名

Question

我有一个如下所示的查询，但不起作用：

SELECT  COUNT(*) as count,  a.COUNT(*) AS total
FROM A
WHERE key IN  (SELECT key
                FROM B
                WHERE condition
               ) a
GROUP BY key;

我也希望得到总数的结果

(SELECT key
 FROM B
 WHERE condition
)

如何为其指定标签，并在外部选择中使用它？还是其他方法？谢谢？

Answer 1

你要找的是一个Left Join

SELECT a.key, sum(b.bCount) as count,  a.COUNT(*) AS total
FROM A
LEFT JOIN  (
   SELECT key, COUNT(1) as bCount
   FROM B
   WHERE condition
) b
on A.key = b.key
GROUP BY key;

Answer 2

如果我正确理解你的目标：

with 
  a1 as (select key, count(*) as cnt from a group by key),
  b1 as (select key, count(*) as cnt from b where condition group by key)
select
  a1.key, a1.cnt, b1.cnt
from
  a1 join b1 on (a1.key = b1.key)

Answer 3

在 PG 中，标识符不区分大小写。因此，在您的查询表中，A和子查询a对服务器来说是一回事，这是不可能的。事实上，这张表可能被称为a，即使你做了CREATE TABLE A (...)。只有在创建时对表名（如 "A" ）双引号，才会保留大小写，但随后还应在所有查询中对表名进行双引号。为子查询提供不同的标签或对表名进行双引号将使错误消失。

在子查询中，如果您希望

该信息在主查询中可用，则需要进行计数。您应该进行两个子查询（因为它们分组到可能不同的不同关系）并在主查询中JOIN它们，而不是相关的子查询 - key IN ...：

SELECT key, sub1.count, sub2.total
FROM (
  SELECT key, count(*) AS count FROM a
  GROUP BY key ) sub1
JOIN (
  SELECT key, count(*) AS total FROM b
  WHERE condition
  GROUP BY key ) sub2 USING (key);