PostgreSQL将两个查询分别与COUNT和GROUP BY合并 [英] PostgreSQL merge two queries with COUNT and GROUP BY in each

问题描述

第一个查询：

1st query:

SELECT date_trunc('day', date1) as date, COUNT(*) AS count_a 
FROM table_a GROUP BY date

结果：

result:

date                    count_a
2014-04-01 00:00:00.0   1011642
2014-04-02 00:00:00.0   309048

第二个查询：

SELECT date_trunc('day', date1) as date, COUNT(*) AS count_b 
FROM table_b GROUP BY date

结果：

result:

date                    count_b
2014-04-01 00:00:00.0   2342
2014-04-02 00:00:00.0   43432

我想得到：

date                    count_a  count_b
2014-04-01 00:00:00.0   1011642  2342
2014-04-02 00:00:00.0   309048   43432

这可能吗？

推荐答案

一个简单的UNION ALL应该使它可能的。

A simple UNION ALL should make it possible.

SELECT date1 date, SUM(a) count_a, SUM(b) count_b 
FROM (
  SELECT DATE_TRUNC('day', date1) date1, 1 a, 0 b FROM table_a 
  UNION ALL 
  SELECT DATE_TRUNC('day', date1) date1, 0 a, 1 b FROM table_b
) z 
GROUP BY date;

编辑：这是一个SQLfiddle 。

这篇关于PostgreSQL将两个查询分别与COUNT和GROUP BY合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！