从posgres上的多个表聚合不起作用

Question

我需要从两个表中计数数据，并在单个输出中显示它们。

我有两张桌子

branches 
atms

我需要像下面这样显示输出

bank_id atms branches
1        20  30   

下面的查询在mysql上运行得很好，但在posgres中却没有任何进展。

SELECT * from (
    (SELECT count(*)  FROM branches where bank_id=30) as branch_count  ,

    (SELECT count(*)   FROM atms where  bank_id=30) as atm_count
    )tmp

Answer 1

如果您只查看一个bank_id，则不需要那么多子查询：

SELECT  (SELECT count(*)  FROM branches where bank_id=30) as branch_count  ,
        (SELECT count(*)   FROM atms where  bank_id=30) as atm_count

但是，如果您想获得所有银行的所有统计数据，假设您有一个表banks来保存所有的bank_ids，那么下面的查询会有帮助：

SELECT  *
FROM    banks B
        LEFT JOIN (SELECT BR.bank_id, count(*) branch_count FROM branches BR GROUP BY BR.bank_id) as BC
             ON BC.bank_id = B.bank_id
        LEFT JOIN (SELECT AT.bank_id, count(*) atm_count  FROM atms AT GROUP BY AT.bank_id) as AC
             ON AC.bank_id = B.bank_id