查询在postgresql中不能正常工作

Select sum(num) as num, sum(numbr) as numbr
from
(
    (Select 0 as num)
    union all
    (Select 1 as num)
) t, 
(
    (Select 2 as numbr)
    union all
    (Select 3 as numbr)
) t1

giving result:

给结果:

num numbr
2   10

But the correct result should be

但正确的结果应该是

num numbr
1   5

4 个解决方案

#1

You are doing the cross product of a table containing 0 and 1, and a table containing 2 and 3. Try removing the sums:

您正在执行包含0和1的表和包含2和3的表的外积。试着删除金额:

Select num, numbr as numbr from 
(
(Select 0 as num)
union all
(Select 1 as num))t, 
((Select 2 as numbr)
union all
(Select 3 as numbr)
)t1

This gives you:

这给你:

0;2
0;3
1;2
1;3

Which will correctly sum to 2 and 10.

正确的和是2和10。

#2

That happens because you are CROSS JOINING , every record connect to every record with out a relation condition, which means that in this case, your join becomes this:

这是因为你是交叉连接的，每个记录连接到每个记录都有一个关系条件，这意味着在这种情况下，你的连接变成了:

NUM | NUMBR
 0      2
 0      3
 1      2  
 1      3

Which SUM(NUM) = 2 and SUM(NUMBR) = 10 .

(NUM) = 2和SUM(NUMBR) = 10。

When joining, you have to specify the relation condition unless this is what you want.

在连接时，您必须指定关系条件，除非这是您想要的。

Note: You are using implicit join syntax(comma separated) , you should avoid that and use the explicit syntax and this will help you make sure you are using a relation condition (by the ON clause):

注意:您正在使用隐式连接语法(逗号分隔)，您应该避免使用这种语法，并使用显式语法，这将帮助您确保使用关系条件(根据ON子句):

Select sum(num) as num, sum(numbr) as numbr
from
(
    (Select 0 as num)
    union all
    (Select 1 as num)
) t
INNER JOIN 
(
    (Select 2 as numbr)
    union all
    (Select 3 as numbr)
) t1
ON(t.<Col> = t1.<Col1>)

#3

Select num, numbr as numbr
from
(
    (Select 0 as num)
    union all
    (Select 1 as num)
) t, 
(
    (Select 2 as numbr)
    union all
    (Select 3 as numbr)
) t1

Gives you the cartessian product of tables.

给出表的cartessian乘积。

| Num | Number |
|-----|--------|
| 0   | 2      |
| 0   | 3      |
| 1   | 2      |
| 1   | 3      |

Therefore the sum of these are 2 and 10

所以它们的和是2和10

#4

Its correctly working as you wrote. If you want the result as you expected, try this:

它像你写的那样工作正常。如果你想要结果如你所期望的那样，试试以下方法:

Select sum(distinct num) as num, sum(distinct numbr) as numbr
from
(
(Select 0 as num)
union all
(Select 1 as num)
) t, 
(
(Select 2 as numbr)
union all
(Select 3 as numbr)
) t1

#1