在单个查询中具有不同约束的不同表上的多个select语句，不显示结果为空

I use several queries on multiple tables that I'd like to combine into a single query for efficiency and speed.

我在多个表上使用了几个查询,我希望将它们组合成一个查询,以提高效率和速度。

My tables look like this:

我的表看起来像这样:

m1
------------------------------------------------------------------------
id  |   time    |   min |   max     |   mean
------------------------------------------------------------------------
a       00:10:00    1       5       3
a       00:20:00    2       6       4
a       00:30:00    3       7       5
a       00:40:00    4       8       6
b       00:40:00    11      15      13
b       00:50:00    21      26      24
c       00:10:00    21      27      25

m2
------------------------------------------------------------------------
id  |   time    |   min |   max     |   mean
------------------------------------------------------------------------
a       00:30:00    22      44      33
a       00:40:00    33      55      44
a       00:50:00    44      66      55
b       00:20:00    1       4       2
b       00:30:00    1       3       2
b       00:30:00    2       5       4
c       00:20:00    6       7       6

m3
------------------------------------------------------------------------
id  |   time    |   min |   max     |   mean
------------------------------------------------------------------------
b       00:10:00    11      15      13
b       00:20:00    21      26      14
c       00:00:00    21      27      25

My queries look like this:

我的查询如下所示:

SELECT min,max,mean FROM m1 WHERE id='a' AND time='00:10:00'
SELECT min,max,mean FROM m2 WHERE id='a' AND time='00:10:00'
SELECT min,max,mean FROM m3 WHERE id='a' AND time='00:10:00'
SELECT min,max,mean FROM m1 WHERE id='a' AND time='00:20:00'
SELECT min,max,mean FROM m2 WHERE id='a' AND time='00:20:00'
SELECT min,max,mean FROM m3 WHERE id='a' AND time='00:20:00'
SELECT min,max,mean FROM m1 WHERE id='a' AND time='00:30:00'
SELECT min,max,mean FROM m2 WHERE id='a' AND time='00:30:00'
SELECT min,max,mean FROM m3 WHERE id='a' AND time='00:30:00'
SELECT min,max,mean FROM m1 WHERE id='a' AND time='00:40:00'
SELECT min,max,mean FROM m2 WHERE id='a' AND time='00:40:00'
SELECT min,max,mean FROM m3 WHERE id='a' AND time='00:40:00'
SELECT min,max,mean FROM m1 WHERE id='a' AND time='00:50:00'
SELECT min,max,mean FROM m2 WHERE id='a' AND time='00:50:00'
SELECT min,max,mean FROM m3 WHERE id='a' AND time='00:50:00'

I take results of these queries and manually put them together into an array that looks like this:

我获取这些查询的结果并手动将它们放在一个如下所示的数组中:

----------------------------------------------------------------------------------------------------------------------------------------------------------------
#|  time    |   m1.min  |   m1.max  |   m1.mean |   m2.min  |   m2.max  |   m2.mean |   m3.min  |   m3.max  |   m3.mean 
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1   00:10:00    1           5           3           NULL        NULL        NULL        NULL        NULL        NULL
2   00:20:00    2           6           4           NULL        NULL        NULL        NULL        NULL        NULL
3   00:30:00    3           7           5           22          44          33          NULL        NULL        NULL
4   00:40:00    NULL        NULL        NULL        33          55          44          NULL        NULL        NULL
5   00:50:00    NULL        NULL        NULL        44          66          55          NULL        NULL        NULL

I have been trying to figure out how to generate the above array with either one sql statement, or one sql statement per time stamp. I've tried joins, unions, case statements, etc. with no luck. The fact that I need to display a 'NULL' or have a blank value in the columns where there are no matching results is what seems to complicate things.

我一直在试图弄清楚如何使用一个sql语句生成上面的数组,或者每个时间戳生成一个sql语句。我试过加入,工会,案例陈述等没有运气。事实上,我需要在没有匹配结果的列中显示“NULL”或空值,这似乎使事情变得复杂。

I am using PostgreSQL 8.3.6.

我正在使用PostgreSQL 8.3.6。

Any help would be greatly appreciated.

任何帮助将不胜感激。

1 个解决方案

#1

The closest solution I found is to use FULL OUTER JOIN like this:

我找到的最接近的解决方案是使用FULL OUTER JOIN,如下所示:

SELECT
    time,
    COALESCE(m1.min::text, 'NULL')  AS "m1.min",
    COALESCE(m1.max::text, 'NULL')  AS "m1.max",
    COALESCE(m1.mean::text, 'NULL') AS "m1.mean",
    COALESCE(m2.min::text, 'NULL')  AS "m2.min",
    COALESCE(m2.max::text, 'NULL')  AS "m2.max",
    COALESCE(m2.mean::text, 'NULL') AS "m2.mean",
    COALESCE(m3.min::text, 'NULL')  AS "m3.min",
    COALESCE(m3.max::text, 'NULL')  AS "m3.max",
    COALESCE(m3.mean::text, 'NULL') AS "m3.mean"
FROM
    (SELECT time, min, max, mean FROM m1 WHERE id = 'a') m1
    FULL JOIN
    (SELECT time, min, max, mean FROM m2 WHERE id = 'a') m2
    USING (time)
    FULL JOIN 
    (SELECT time, min, max, mean FROM m3 WHERE id = 'a') m3
    USING (time)
ORDER BY time;

Result:

   time   | m1.min | m1.max | m1.mean | m2.min | m2.max | m2.mean | m3.min | m3.max | m3.mean 
----------+--------+--------+---------+--------+--------+---------+--------+--------+---------
 00:10:00 | 1      | 5      | 3       | NULL   | NULL   | NULL    | NULL   | NULL   | NULL
 00:20:00 | 2      | 6      | 4       | NULL   | NULL   | NULL    | NULL   | NULL   | NULL
 00:30:00 | 3      | 7      | 5       | 22     | 44     | 33      | NULL   | NULL   | NULL
 00:40:00 | 4      | 8      | 6       | 33     | 55     | 44      | NULL   | NULL   | NULL
 00:50:00 | NULL   | NULL   | NULL    | 44     | 66     | 55      | NULL   | NULL   | NULL
(5 rows)