所以我有两个付款表，我想在Oracle SQL数据库中进行比较。我想比较使用地点和发票的总付款和总付款。它比这个更复杂，但基本上是：
select
tbl1.location,
tbl1.invoice,
Sum(tbl1.payments),
Sum(tbl2.payments)
From
tbl1
left outer join tbl2 on
tbl1.location = tbl2.location
and tbl1.invoice = tbl2.invoice
group by
(tbl1.location,tbl1.invoice)
我想要左边的外部联接，因为除了比较付款金额外，我还想检查tbl1中可能不存在的所有订单。
问题是，两个表中的每个订单（地点和发票）都有多条记录（记录数量不一定相同，即tbl1中的2条到tbl2中的1条，反之亦然），但每个订单（位置和发票）的总付款额应该匹配。所以直接连接就得到了一个笛卡尔乘积。
所以我想我可以做两个查询，第一个是按商店汇总付款总额；为每个订单开具发票，然后对这些结果进行联接，因为在聚合结果中，我对每个订单只有一条记录（商店和发票）。但我不知道该怎么做。我已经尝试了几个子查询，但似乎无法动摇笛卡尔乘积。我希望能够在一个查询中做到这一点，而不是创建表并加入这些表，因为这将持续下去。
提前感谢您的帮助。
您可以使用With语句创建两个查询，然后按照您所说的进行连接。我只会付出代价，如果你需要更多的帮助，就去问。那是因为你没有提供你桌子上的全部细节。所以我只是猜测我的答案。
WITH tmpTableA as (
select
tbl1.location,
tbl1.invoice,
Sum(tbl1.payments) totalTblA
From
tbl1
group by
tbl1.location,
tbl1.invoice
),
tmpTableB as (
select
tbl2.location,
tbl2.invoice,
Sum(tbl2.payments) totalTblB
From
tbl2
group by
tbl2.location,
tbl2.invoice
)
Select tmpTableA.location, tmpTableA.invoice, tmpTableA.totalTblA,
tmpTableB.location, tmpTableB.invoice, tmpTableB.totalTblB
from tmpTableA, tmpTableB
where tmpTableA.location = tmpTableB.location (+)
and tmpTableA.invoice = tmpTableB.invoice (+)
(+)运算符是Oracle数据库的left join运算符（当然，如果您愿意，可以使用LEFT JOIN语句）
另外两个选项：
SQL Fiddle
Oracle 11g R2架构设置:
CREATE TABLE tbl1 ( id, location, invoice, payments ) AS
SELECT 1, 'a', 1, 1 FROM DUAL
UNION ALL SELECT 2, 'a', 1, 1 FROM DUAL
UNION ALL SELECT 3, 'a', 1, 1 FROM DUAL
UNION ALL SELECT 4, 'a', 1, 1 FROM DUAL
UNION ALL SELECT 5, 'a', 1, 1 FROM DUAL
UNION ALL SELECT 6, 'a', 2, 1 FROM DUAL
UNION ALL SELECT 7, 'a', 2, 1 FROM DUAL
UNION ALL SELECT 8, 'a', 2, 1 FROM DUAL
UNION ALL SELECT 9, 'b', 1, 1 FROM DUAL
UNION ALL SELECT 10, 'b', 2, 1 FROM DUAL;
CREATE TABLE tbl2 ( id, location, invoice, payments ) AS
SELECT 1, 'a', 1, 1 FROM DUAL
UNION ALL SELECT 2, 'a', 1, 1 FROM DUAL
UNION ALL SELECT 3, 'a', 1, 1 FROM DUAL
UNION ALL SELECT 4, 'a', 2, 1 FROM DUAL
UNION ALL SELECT 5, 'a', 2, 1 FROM DUAL
UNION ALL SELECT 6, 'b', 1, 1 FROM DUAL
UNION ALL SELECT 7, 'b', 1, 1 FROM DUAL
UNION ALL SELECT 8, 'b', 1, 1 FROM DUAL
UNION ALL SELECT 9, 'b', 1, 1 FROM DUAL
UNION ALL SELECT 10, 'b', 1, 1 FROM DUAL;
查询1：
这一个使用相关的子查询来计算第二个表的总数：
SELECT location,
invoice,
SUM( payments ) AS total_payments_1,
COALESCE( (SELECT SUM( payments )
FROM tbl2 i
WHERE o.location = i.location
AND o.invoice = i.invoice),
0 ) AS total_payments_2
FROM tbl1 o
GROUP BY
location,
invoice
ORDER BY
location,
invoice
结果：
| LOCATION | INVOICE | TOTAL_PAYMENTS_1 | TOTAL_PAYMENTS_2 |
|----------|---------|------------------|------------------|
| a | 1 | 5 | 3 |
| a | 2 | 3 | 2 |
| b | 1 | 1 | 5 |
| b | 2 | 1 | 0 |
查询2：
这一个使用命名子查询来预先计算表1的总计，然后对第二个表执行LEFT OUTER JOIN，并将表1的合计包括在组中。
在没有任何索引的情况下，从解释计划来看，查询1似乎效率高得多，但索引可能意味着优化器会找到更好的计划。
WITH tbl1_sums AS (
SELECT location,
invoice,
SUM( payments ) AS total_payments_1
FROM tbl1
GROUP BY
location,
invoice
)
SELECT t1.location,
t1.invoice,
t1.total_payments_1,
COALESCE( SUM( t2.payments ), 0 ) AS total_payments_2
FROM tbl1_sums t1
LEFT OUTER JOIN
tbl2 t2
ON ( t1.location = t2.location
AND t1.invoice = t2.invoice)
GROUP BY
t1.location,
t1.invoice,
t1.total_payments_1
ORDER BY
t1.location,
t1.invoice
结果：
| LOCATION | INVOICE | TOTAL_PAYMENTS_1 | TOTAL_PAYMENTS_2 |
|----------|---------|------------------|------------------|
| a | 1 | 5 | 3 |
| a | 2 | 3 | 2 |
| b | 1 | 1 | 5 |
| b | 2 | 1 | 0 |
对不起，我的第一个答案是错误的。感谢您提供sqlfiddle，MT0。
我遗漏的一点是，你需要先汇总每张桌子上的付款，所以每张桌子只剩下一行，然后再加入它们。这就是MT0在声明中所做的。
如果你想要一个看起来更"对称"的解决方案，请尝试：
select A.location, A.invoice, B.total sum1, C.total sum2
from (select distinct location, invoice from tbl1) A
left outer join (select location, invoice, sum(payments) as total from tbl1 group by location, invoice) B on A.location=B.location and A.invoice=B.invoice
left outer join (select location, invoice, sum(payments) as total from tbl2 group by location, invoice) C on A.location=C.location and A.invoice=C.invoice
导致
LOCATION INVOICE SUM1 SUM2
a 2 3 2
a 1 5 3
b 1 1 5
b 2 1 (null)