如何编写SQL 2005查询

问题描述

我有一个表结构为

I have a table structure as

Amt     Name    TransType
20	abc	buy
10	abc	buy
15	abc	buy
8	abc	sell
2	abc	sell

我想编写一个查询，将结果集设置为

TotalAmt |名称
45 abc
-10 abc
35 GrandAbc

我已经尝试过但没有得到.

I want to write a query which give me result set as

TotalAmt | Name
45 abc
-10 abc
35 GrandAbc

I have tried but not getting it.

select  case when trantype='buy' then sum(amt) when trantype='sell' then ((-1)*sum(amt)) end as Total,Name
  from stock group by trantype
union all
select -----
but it is not giving the output as above

推荐答案

SELECT
SUM(
CASE Transtype
	WHEN 'Buy' THEN amt
	WHEN 'Sell' THEN (amt*-1)
END)
 AS Total
FROM #tbl

DECLARE @BOUGHT INT, @SOLD INT

SET @BOUGHT = (SELECT SUM(amt) FROM tableAbc WHERE transtype = 'buy')
SET @SOLD = (SELECT SUM(amt) FROM tableAbc WHERE transtype = 'sell')

SELECT @BOUGHT - @SOLD

这篇关于如何编写SQL 2005查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！