sql - 在公共(public)列上加入 2 个 SELECT 语句

Question

我有两个选择语句，如下所示:

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

这两个表的结果都有“名称”的公共(public)列 我想合并这两个 SELECT 语句，所以我有 1 个名称列，并且两个总和都显示在它们共同发起的名称旁边。

我已经尝试过 FULL OUTER JOIN，但我似乎无法让它工作。

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

Answer 1

在值为 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 fiddle