SQL在另一个查询中使用UNION查询的结果

Daniel Chen 发表于 Dev

丹尼尔·陈（Daniel Chen）

如何完成此查询？

现在，我正在使用的查询就是这个，但它不能产生正确的数据。

SELECT date, coalesce(count,0) AS count 
FROM 
    generate_series(
        '2014-12-13 12:00:00'::timestamp, 
        '2015-01-06 11:00:00'::timestamp, 
        '1 hour'::interval
    ) AS date 
    LEFT OUTER JOIN (
        SELECT 
            date_trunc('day', TABLE1.created_at) as day, 
            count(DISTINCT TABLE1.user) as count 
        FROM TABLE1 
        WHERE org_id = 1 
        GROUP BY day
    ) results ON (date = results.day);

代替TABLE1，我需要向查询提供另一个查询的数据，如下所示：

SELECT TABLE2.user_a as userid, TABLE2.created_at as createdat from TABLE2 
UNION ALL 
SELECT TABLE3.user_b as userid, TABLE3.created_at as createdat from TABLE3 
UNION ALL 
SELECT TABLE4.sender as userid, TABLE4.created_at as createdat from TABLE4;

我该怎么做呢？

穆雷尼克

select接收表的查询的任何部分（例如，from子句，join子句等）都可以接收括号中的查询-这称为子查询。请注意，在Postgres中，必须给该子查询一个别名（即可以被其引用的名称）。因此，在您的情况下：

SELECT date, coalesce(count,0) AS count 
FROM 
    generate_series(
        '2014-12-13 12:00:00'::timestamp, 
        '2015-01-06 11:00:00'::timestamp, 
        '1 hour'::interval
    ) AS date 
    LEFT OUTER JOIN (
        SELECT 
            date_trunc('day', subquery.created_at) as day, 
            count(DISTINCT subquery.user) as count 
              -- Subquery here:
        FROM (SELECT TABLE2.user_a as userid, TABLE2.created_at as createdat from TABLE2 
              UNION ALL 
              SELECT TABLE3.user_b as userid, TABLE3.created_at as createdat from TABLE3 
              UNION ALL 
              SELECT TABLE4.sender as userid, TABLE4.created_at as createdat from TABLE4) 
             subquery 
        WHERE org_id = 1 
        GROUP BY day
    ) results ON (date = results.day);