仅使用select和case语句内联分别查找正数和负数的总和

sshcrbum 于 2021-06-19 发布在 Mysql

关注(0)|答案(3)|浏览(266)

我有个问题。如果给我一张有正数和负数的表，我想分别找出所有正数和负数的总和，并将其显示在一行中。我能想出一个解决办法（见下文）。我想看看是否有可能有一个更好的解决方案，使用select和case语句

id  amount
1   100
2   -10
3   50
4   -80
5   20
6   -20

positive negative
170         -110

我的解决方案：

create table #temp (id int, amount int)
insert into #temp values (1, 100)
insert into #temp values (2, -10)
insert into #temp values (3, 50)
insert into #temp values (4, -80)
insert into #temp values (5, 20)
insert into #temp values (6, -20)

with positive as 
(select sum(amount) as posNum from temp where amount > 0)
, negative as 
(select sum(amount) as negNum from temp where amount < 0)
select *, (select * from negative) from positive

mysql sql-server

来源：https://stackoverflow.com/questions/52548479/find-total-of-positive-and-negative-numbers-separately-inline-using-only-select

3条答案

按热度按时间

a7qyws3x1#

你也可以使用 sign() 确定它是正数还是负数

select  sum(case when sign(amount) = 1 then amount end),
        sum(case when sign(amount) = -1 then amount end)
from    #temp

赞(0）回复(0）举报 2021-06-20

kr98yfug2#

使用条件聚合：https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=e2fa9cc6aee27cc501c9cda98bc2cf2d

SELECT
    SUM(CASE WHEN amount > 0 THEN amount END) AS positive,
    SUM(CASE WHEN amount < 0 THEN amount END) AS negative
FROM #temp;

输出：

positive    negetive
170         -110

赞(0）回复(0）举报 2021-06-20

nzrxty8p3#

这将起作用：
1）对于sql server

SELECT
    SUM(IIF(amount > 0, amount, 0)) positive,
    SUM(IIF(amount < 0, amount, 0)) negative
FROM TABLENAME;

2）对于sql server和mysql

SELECT
    SUM(CASE WHEN amount > 0 THEN amount END) positive,
    SUM(CASE WHEN amount < 0 THEN amount END) negative
FROM TABLENAME;

赞(0）回复(0）举报 2021-06-19

我来回答

仅使用select和case语句内联分别查找正数和负数的总和

3条答案

相关问题

热门标签

最新问答