在公共列上加入2个SELECT语句(Join 2 SELECT statements on common column)

在公共列上加入2个SELECT语句(Join 2 SELECT statements on common column)

我有两个选择语句，如bellow：

SELECT t.name, SUM(t.number) FROM table1 t WHERE t.something = '1' GROUP BY t.name SELECT v.name, SUM(v.number2) FROM table2 v WHERE v.somethingElse = '2' GROUP BY v.name

这两个表的结果都有“name”的公共列我想要组合两个SELECT语句，所以我有1个名字列，两个总和显示在他们的共同点名称旁边。

我已经尝试了一个完整的外部加入，但我似乎无法让它工作。

SELECT t.name, SUM(t.number) FROM table1 t FULL OUTER JOIN (SELECT v.name, SUM(v.number2) FROM table2 v WHERE v.somethingElse = '2' GROUP BY v.name) ON t.name = v.name WHERE t.something = '1' GROUP BY t.name

希望有人可以指出我的愚蠢错误或我应该怎么做。

谢谢。

更新：继续收到错误'缺少正确的括号'。 看看sqlfiddle http://sqlfiddle.com/#!4/b04c4/4

I have two select statements such as bellow:

SELECT t.name, SUM(t.number) FROM table1 t WHERE t.something = '1' GROUP BY t.name SELECT v.name, SUM(v.number2) FROM table2 v WHERE v.somethingElse = '2' GROUP BY v.name

The result from both of these tables have the common column of 'name' I want to combine the two SELECT statements so I have 1 name column and both the sums show next to their cosponsoring name.

I have tried a FULL OUTER JOIN but I cannot seems to get it to work.

SELECT t.name, SUM(t.number) FROM table1 t FULL OUTER JOIN (SELECT v.name, SUM(v.number2) FROM table2 v WHERE v.somethingElse = '2' GROUP BY v.name) ON t.name = v.name WHERE t.something = '1' GROUP BY t.name

Hope someone can point out my silly mistake or how I should go about doing this.

Thank you.

UPDATE: Keep getting error 'Missing right parenthesis'. Have a look on sqlfiddle http://sqlfiddle.com/#!4/b04c4/4

最满意答案

在值为0的子查询上创建虚拟列，并使用union all按名称对它们求和。 像这样的东西

select name, sum(number1),sum(number2) from ( SELECT t.name, SUM(t.value) as number1 , 0 as number2 FROM table1 t WHERE t.value = '1' GROUP BY t.name UNION SELECT v.name, 0 as number1 ,SUM(v.value) as number2 FROM table2 v WHERE v.value = '2' GROUP BY v.name ) group by name;

这是sql小提琴

Create the virtual column on subqueries with values 0 and them sum them all by names using union all. Something like this

select name, sum(number1),sum(number2) from ( SELECT t.name, SUM(t.value) as number1 , 0 as number2 FROM table1 t WHERE t.value = '1' GROUP BY t.name UNION SELECT v.name, 0 as number1 ,SUM(v.value) as number2 FROM table2 v WHERE v.value = '2' GROUP BY v.name ) group by name;

here it is on sql fiddle