sql - 从postgres的联合结果中选择

我想问一下：

select currency, sum(volume)
from
    (
        select "baseCurrency" as currency,
               COALESCE("baseVolume", 0) as volume
        from "Pairs"
    )  as a
    union all
    (
        select "quoteCurrency" as currency,
               COALESCE("quoteVolume", 0) as volume
        from "Pairs"
    ) as b
group by currency

但无论我如何更改查询，都会出现错误。例如：

ERROR:  syntax error at or near "as"
LINE 11:  ) as b

这个查询有什么问题？

最佳答案

不要对union all中的子查询使用别名：

select currency, sum(volume)
from ((select "baseCurrency" as currency, COALESCE("baseVolume", 0) as volume
       from "Pairs"
       ) union all
       (select "quoteCurrency" as currency, COALESCE("quoteVolume", 0) as volume
        from "Pairs"
       )
      ) c
group by currency;

在Postgres的最新版本中，您可以使用横向join：

select v.currency, sum(v.volume)
from "Pairs" p cross join lateral
     (values ("baseCurrency", "baseVolume"),
             ("quoteCurrency", "quoteVolume")
     ) v(currency, volume)
group by v.currency;

coalesce()可能是不必要的。如果得到NULL结果，可以在sum()之后处理。. . coalesce(sum(volume), 0)。

关于sql - 从postgres的联合结果中选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53584865/