我的sum查询返回2行，需要一些建议吗

f3temu5u 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(314)

我得到2行（9500）为111的结果，请您建议正确的方法，我需要像，平衡=（总和买入-总和卖出）


# Table T1#

+---------+--------+------+------+  
| ACCOUNT | TRANS  | AMT  | YEAR |  
+---------+--------+------+------+  
|     111 | BOUGHT | 8000 | 2019 |  
|     111 | BOUGHT | 2000 | 2019 | 
|     111 | SOLD   |  500 | 2019 |  
|     222 | BOUGHT | 6000 | 2018 |  
|     222 | SOLD   |  300 | 2018 |  
+---------+--------+------+------+

查询

SELECT (A.BOUGHTs - B.SOLDs) AS BALANCE  
  FROM T1 
 INNER JOIN  
  (SELECT SUM(AMT) AS BOUGHTs  
     FROM T1  
    WHERE TRANS = 'BOUGHT'  
  ) A  
   ON T1.ACCOUNT = A.ACCOUNT  
 INNER JOIN  
  (SELECT SUM(AMT) AS SOLDs  
     FROM T2  
    WHERE TRANS = 'SOLD'  
  ) B  
   ON T1.ACCOUNT = B.ACCOUNT  
WHERE T1.ACCOUNT = 111  
  AND T1.YEAR    = 2019

sql mysql sum

来源：https://stackoverflow.com/questions/62861319/my-sum-query-returns-2-rows-need-some-advise

2条答案

按热度按时间

ha5z0ras1#

可以使用条件聚合：

select account, year,
       sum(case when trans = 'bought' then amt 
                when trans = 'sold' then - amt
                else 0
           end) as diff
from t
group by account, year;

如果您只需要一个帐户，您可以添加一个 where 条款：

select account, year,
       sum(case when trans = 'bought' then amt 
                when trans = 'sold' then - amt
                else 0
           end) as diff
from t
where account = 111 and year = 2019
group by account, year;

赞(0）回复(0）举报 2021-07-26

dly7yett2#

重写查询将如下所示：

SELECT (A.BOUGHTs - B.SOLDs) AS BALANCE
  FROM
    (SELECT ACCOUNT,YEAR,SUM(AMT) AS BOUGHTs FROM T1 WHERE TRANS = 'BOUGHT' GROUP BY ACCOUNT,YEAR) A
    INNER JOIN
    (SELECT ACCOUNT,YEAR,SUM(AMT) AS SOLDs FROM T1 WHERE TRANS = 'SOLD' GROUP BY ACCOUNT,YEAR) B
    ON A.ACCOUNT=B.ACCOUNT AND A.YEAR=B.YEAR
WHERE A.ACCOUNT = 111
  AND A.YEAR    = 2019;

赞(0）回复(0）举报 2021-07-26

我来回答

我的sum查询返回2行，需要一些建议吗

2条答案

相关问题

热门标签

最新问答