SQL查询 - 如何获得行的单元格的倍数总和的差异

  • Z时代
  • 2024-01-10
  • 分类：问答

我需要获得单个表格中两个字段的总和的差异（真的很抱歉，如果这是令人困惑），请阅读例子SQL查询 - 如何获得行的单元格的倍数总和的差异

Id type account_id stock_id volume price value 
========================================================== 
1 BUY  1   1    5  500  2500 
2 BUY  1   4   30  200  6000 
6 BUY  1   1   10  500  5000 
7 SELL  1   1    3  500  1500 
8 SELL  1   1    2  500  1000 
9 SELL  1   4   20  120  2400

以上就是我的样本数据，我将我的SQL查询结果是这样的，

account_id stock_id volume totalAmount 
============================================ 
1   1   10  5000 
1   4   10  3600

基本上在这里，我想获得的唯一帐户&股票组合的总购买价值减去总销售价值为

任何帮助在这里将不胜感激。

在此先感谢

回答：

小提琴测试： http://sqlfiddle.com/#!2/53035/1/0

select account_id, 
     stock_id, 
     sum(case when type = 'BUY' then volume else -volume end) as volume, 
     sum(case when type = 'BUY' then value else -value end) as totalamount 
from  tbl 
group by account_id, 
     stock_id 
having sum(case when type = 'BUY' then volume else -volume end) <> 0

我增加了一个基于您的评论HAVING子句。

回答：

只是为了减少重复我会改变Brian的代码如下：

SELECT 
    account_id, 
    stock_id, 
    SUM(volume * type_sign) as total_volume, 
    SUM(value * type_sign) as total_value 
FROM 
    (select t.*, case when type = 'BUY' then 1 else -1 end as type_sign 
    from tbl) t 
GROUP BY account_id, 
     stock_id

回答：

select buy.account_id,buy.stock_id,(buy.volume-sell.volume) volume,(buy.totalAmount-sell.totalAmount) totalAmount from 
(select account_id,stock_id,sum(volume) volume,sum(value) totalAmount from stock 
where type = 'BUY' 
group by account_id,stock_id) buy 
inner join 
(select account_id,stock_id,sum(volume) volume,sum(value) totalAmount from stock 
where type = 'SELL' 
group by account_id,stock_id) sell 
on buy.account_id = sell.account_id and buy.stock_id = sell.stock_id

以上是 SQL查询 - 如何获得行的单元格的倍数总和的差异 的全部内容， 来源链接： utcz.com/qa/264679.html

回到顶部