sql中两个不同表之和的差异
sql中两个不同表之和的差异
提问于 2013-09-15 02:52:24
我有两个sql表，purchase和sent，我想知道项目在任何日期的未偿还余额。为此，我尝试使用sum(puchase.quantity) - sum(sent.quantity) where date<some date。我可以有单独的查询，但我不知道如何组合它们。
以下是单独的查询：
select item,sum(quantity) from sent where datee <'some date' group by item
select item,sum(quantity) from purchase where datee<'some date' group by item请告诉我是否有更好的方法来获得未偿还的余额。
提前谢谢。
回答 2
Stack Overflow用户
发布于 2013-09-15 03:04:02
我觉得你想要这个
下面是有效的演示
CREATE TABLE sent
(
item int,
quantity int
);
CREATE TABLE purchase
(
item int,
quantity int
);
insert into sent values(1,4);
insert into sent values(2,7);
insert into sent values(3,9);
insert into sent values(4,5);
insert into sent values(5,9);
insert into purchase values(1,2);
insert into purchase values(2,5);
insert into purchase values(3,3);
insert into purchase values(4,2);
insert into purchase values(5,7);
select sent.item , (sum(IFNULL(sent.quantity,0)) - sum(IFNULL(purchase.quantity,0))) as diff
FROM sent , purchase Where sent.item = purchase.item group by sent.itemStack Overflow用户
发布于 2013-09-15 06:28:23
也许有一种更简单的方法可以得到你想要的东西，但下面的方法应该会给你正确的结果：
SELECT s.item,
s.qty as total_sent,
COALESCE(p.qty,0) as total_purchase,
s.qty - COALESCE(p.qty,0) as stock
FROM
(
SELECT item, sum(quantity) as qty
FROM sent
GROUP BY item
) s
LEFT JOIN
(
SELECT item, sum(quantity) as qty
FROM purchase
GROUP BY item
) p
ON p.item = s.item页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18805277
复制相关文章
相似问题
腾讯云开发者
