左连接和与比较

Question

我有一个股票购买程序，其中可以存在多个卖出交易，每购买交易。我正在尝试创建一个查询，它将提取任何我还在投资的股票。例如，如果我买了一只股票的50股，一天卖出20股，另一天卖出10股，我应该还剩下20股。我已经做了大部分艰苦的工作，但我发誓我错过了一些小的东西。如果sell_transaction表中不存在结果，则当前的查询将不会提取结果。在我的示例中，这是buy_transactions中的buy_transactions 3，它应该返回100股，但什么也不返回。可以将以下代码放入SQL并进行处理。

模式

CREATE TABLE `buy_transactions` (
`buy_transactions_id` int(11),
`buy_transaction_date` date,
`symbol` varchar(50),
`shares` int(11),
`price_per_shae` decimal(10,6));

insert into buy_transactions values (1,'2016-01-25','A',15,100.000000);
insert into buy_transactions values (2,'2014-03-16','A',20,30.000000);
insert into buy_transactions values (3,'2016-01-15','AA',100,60.000000);
insert into buy_transactions values (4,'2015-05-05','AA',500,60.000000);

CREATE TABLE `sell_transactions` (
`sell_transactions_id` int(11) NOT NULL AUTO_INCREMENT,
`sell_transaction_date` varchar(45) DEFAULT NULL,
`shares` int(11) DEFAULT NULL,
`price` decimal(10,6) DEFAULT NULL,
`related_buy_transaction` int(11) DEFAULT NULL,
PRIMARY KEY (`sell_transactions_id`));

insert into sell_transactions values (1, '2016-01-25', 5, 120.000000, 1);
insert into sell_transactions values (2, '2016-01-25', 10, 130.000000, 1);
insert into sell_transactions values (3, '2016-01-25', 10, 50.000000, 2);
insert into sell_transactions values (4, '2016-01-15', 500, 61.000000, 4);

当前查询

select bt.buy_transactions_id, bt.symbol, bt.shares - rt.SoldShares as remaining_stock
from buy_transactions bt
left join
    (select related_buy_transaction, sum(shares) as SoldShares from sell_transactions group by related_buy_transaction) rt
on bt.buy_transactions_id = rt.related_buy_transaction
where bt.shares - rt.SoldShares > 0;

当前查询结果

buy_transactions_id     symbol  remaining_stock
2                       A       10

SQLFiddle

Answer 1

使用coalesce

select bt.buy_transactions_id 
     , bt.symbol
     , bt.shares - coalesce(rt.SoldShares, 0) as remaining_stock
from buy_transactions bt
left join
( select related_buy_transaction
       , sum(shares) as SoldShares
  from sell_transactions
  group by related_buy_transaction ) rt on bt.buy_transactions_id = rt.related_buy_transaction
where bt.shares > coalesce(rt.SoldShares, 0);

SQLFiddle