--> 数据库版本: --> 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
select sum(case when 方向 = '收' then 金额 else -金额 end) from tb
sum(case when 方向='付' then 金额 else 0 end) as 付, sum(case when 方向='收' then 金额 else 0 end) as 收,
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 tbdrop table tb/*
----------- 400(所影响的行数为 1 行) */
select 字段名称 ,剩余 = sum(case when 方向 = '收' then 金额 else -金额 end) from 表A group by 字段名称
--> 数据库版本: --> 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
--> 数据库版本:
--> 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
sum(case when 方向='收' then 金额 else 0 end) as 收,
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 tbdrop table tb/*
-----------
400(所影响的行数为 1 行)
*/
,剩余 = sum(case when 方向 = '收' then 金额 else -金额 end)
from 表A
group by 字段名称
--> 数据库版本:
--> 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