分组求和问题

[qq574444572]

表A 字段名称、金额、方向
记录：0001 1000 付
0002 2000 收
0003 800 付
0004 200 收
用SQL语句实现：SUM(金额）（收）-sum(金额）（付）

[王向飞]

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 
AND type in (N'U')) 
DROP TABLE [TB]
GO

---->建表
create table [TB]([名称] varchar(4),[金额] int,[方向] varchar(2))
insert [TB]
select '0001',1000,'付' union all
select '0002',2000,'收' union all
select '0003',800,'付' union all
select '0004',200,'收'
GO



--> 查询结果
SELECT * FROM [TB]


select  名称,
 SUM(case when 方向='付' then 金额 else 0 end ) as 总付  ,
 SUM(case when 方向='收' then 金额 else 0 end ) as 总收  
from TB
group by 名称
--> 删除表格
--DROP TABLE [TB]


--名称	总付	总收
0001	1000	0
0002	0	2000
0003	800	0
0004	0	200

[dawugui]

select sum(case when 方向 = '收' then 金额 else -金额 end) from tb

[Zoezs]

sum(case when 方向='付' then 金额 else 0 end) as 付,
sum(case when 方向='收' then 金额 else 0 end) as 收,

[dawugui]

create table [TB]([名称] varchar(4),[金额] int,[方向] varchar(2))
insert [TB]
select '0001',1000,'付' union all
select '0002',2000,'收' union all
select '0003',800,'付' union all
select '0004',200,'收'

select sum(case when 方向 = '收' then 金额 else -金额 end) from tb

drop table tb

/*
            
----------- 
400

（所影响的行数为 1 行）
*/

[budong0000]

select 字段名称
,剩余 = sum(case when 方向 = '收' then 金额 else -金额 end)
from 表A
group by 字段名称

[qq574444572]

[Quote=引用 1 楼 wxf163 的回复:]
SQL code

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE……
[/Quote]
要的是 sum

[王向飞]

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]') 
AND type in (N'U')) 
DROP TABLE [TB]
GO

---->建表
create table [TB]([名称] varchar(4),[金额] int,[方向] varchar(2))
insert [TB]
select '0001',1000,'付' union all
select '0002',2000,'收' union all
select '0003',800,'付' union all
select '0004',200,'收'
GO



--> 查询结果
SELECT * FROM [TB]


select   
 SUM(case when 方向='付' then 金额 else 0 end ) as 总付  ,
 SUM(case when 方向='收' then 金额 else 0 end ) as 总收,
 SUM(case when 方向='收' then 金额 else -金额 end ) as 总收
from TB
 
--> 删除表格
--DROP TABLE [TB]


--总付	总收	总收
1800	2200	400

[dawugui]

[Quote=引用 6 楼 qq574444572 的回复:]
要的是 sum[/Quote]2,4楼。

[qq574444572]

[Quote=引用 7 楼 wxf163 的回复:]
SQL code

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据：[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE……
[/Quote]
帅死 就这个