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

Question

我有个问题。如果给我一个包含正数和负数的表格，我想分别查找所有正数的总数和所有负数的总数，并将其显示在一行中。我能够想出一种解决方案（如下）。我想检查是否可以使用 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 

标签： mysqlsql-server

Answer 1

使用条件聚合：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