MySQL - 从一个查询的结果中扣除另一个查询的结果

Question

我正在运行两个查询。

它们在这里:

第一个查询

SELECT SUM(dbase1.transaction.amount) AS total, dbase1.player.name 
FROM dbase1.transaction
JOIN dev2.player ON dbase1.transaction.player_id = dbase1.player.player_id
WHERE (dbase1.transaction.type !=1)
AND (dbase1.transaction.type !=3) 
GROUP BY dbase1.transaction.player_id 
ORDER BY name ;

第二个查询

SELECT dbase2.transaction.amount
, dbase2.transaction.player_id
, dbase2.player.name 
FROM dbase2.transaction
JOIN dbase2.player ON dbase2.transaction.player_id = dbase2.player.player_id
WHERE dbase2.transaction.provider ='providerx';

在第一个结果集中，我得到一个金额和一个名称，在第二个结果集中，我得到一个金额、一个名称和一个 ID。

我想要的是从名称匹配的第一组结果中扣除第二组结果中的金额。

所以基本上我的最终结果是:

amount from set1 - amount from set2, name, id

我希望这是有道理的，任何建议将不胜感激

谢谢

Answer 1

加入他们吧。

select a.total - b.amount from (
    select sum(dbase1.transaction.amount)
    as total, dbase1.player.name from dbase1.transaction
    JOIN dev2.player on
    dbase1.transaction.player_id = dbase1.player.player_id
    where
    (dbase1.transaction.type !=1)
    and
    (dbase1.transaction.type !=3) group by dbase1.transaction.player_id
) query_a a
INNER JOIN 
(
    select dbase2.transaction.amount, dbase2.transaction.player_id, dbase2.player.name from dbase2.transaction
    JOIN dbase2.player ON
    dbase2.transaction.player_id = dbase2.player.player_id
    where dbase2.transaction.provider ='providerx'
) query_b b on a.name = b.name