如何跨两个不相关的表进行 SUM? [英] How to do a SUM across two unrelated tables?
问题描述
我试图用 postgres 对两个不相关的表进行总结.使用 MySQL,我会做这样的事情:
SELECT SUM(table1.col1) AS sum_1, SUM(table2.col1) AS sum_2 FROM table1, table2
这应该给我一个包含两列名为 sum_1 和 sum_2 的表格.但是,postgres 没有给我这个查询的任何结果.
有什么想法吗?
SELECT (SELECT SUM(table1.col1) FROM table1) AS sum_1,(SELECT SUM(table2.col1) FROM table2) AS sum_2;
你也可以写成:
SELECT t1.sum_c1, t1.sum_c2, t2.sum_t2_c1从(SELECT SUM(col1) sum_c1,SUM(col2) sum_c2从表 1) t1全外连接(SELECT SUM(col1) sum_t2_c1从表 2) t2 ON 1=1;
FULL JOIN 与无用条件一起使用,以便任何一个子查询都不会产生结果(空),而不会导致更大的查询没有结果.
我不认为您编写的查询会产生您希望得到的结果,因为它在 table1 和 table2 之间进行了 CROSS JOIN,这会使每个 SUM 膨胀另一个表中的行数.请注意,如果 table1/table2 为空,则 CROSS JOIN 将导致 X 行乘 0 行返回空结果.
看看这个 SQL Fiddle 并比较结果.>
I'm trying to sum on two unrelated tables with postgres. With MySQL, I would do something like this :
SELECT SUM(table1.col1) AS sum_1, SUM(table2.col1) AS sum_2 FROM table1, table2
This should give me a table with two column named sum_1 and sum_2. However, postgres doesn't give me any result for this query.
Any ideas?
SELECT (SELECT SUM(table1.col1) FROM table1) AS sum_1,
(SELECT SUM(table2.col1) FROM table2) AS sum_2;
You can also write it as:
SELECT t1.sum_c1, t1.sum_c2, t2.sum_t2_c1
FROM
(
SELECT SUM(col1) sum_c1,
SUM(col2) sum_c2
FROM table1
) t1
FULL OUTER JOIN
(
SELECT SUM(col1) sum_t2_c1
FROM table2
) t2 ON 1=1;
The FULL JOIN is used with a dud condition so that either subquery could produce no results (empty) without causing the greater query to have no result.
I don't think the query as you have written would have produced the result you expected to get, because it's doing a CROSS JOIN between table1 and table2, which would inflate each SUM by the count of rows in the other table. Note that if either table1/table2 is empty, the CROSS JOIN will cause X rows by 0 rows to return an empty result.
Look at this SQL Fiddle and compare the results.
这篇关于如何跨两个不相关的表进行 SUM?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!