今天看到财务的一个表,想学习一下,可是没有头绪;日期 类别 项目 金额 凭证号
2014-01-01 收入 A 40000 XS-001
2014-01-01 支出 B 2000 XF-001
2014-01-01 支出 C 5000 XF-002
2014-01-02 支出 D 12000 XF-003
2014-01-02 支出 B 1000 XF-004
2014-01-03 支出 E 500 XF-005
.......2014-01-10 收入 A 20000 XS -002
2014-01-10 支出 C 3000 XF-020
......要求结果是:
日期 凭证号 类别 项目 收入 支出 余额
‘’ '上期结转' ‘’ ‘’ 0 0 23000
2014 -01-01 XS-001 收入 A 40000 0 63000
2014-01-01 XF-001 支出 B 2000 61000
2014-01-01 XF-002 支出 C 5000 56000
2014-01-02 XF-003 支出 D 12000 44000
..... 10800
2014-01-10 XS-002 收入 A 20000 0 30800
2014-01-10 XF--020 支出 C 5000 25800
。。
‘小计’ 60000 80000 3000
2014-01-01 收入 A 40000 XS-001
2014-01-01 支出 B 2000 XF-001
2014-01-01 支出 C 5000 XF-002
2014-01-02 支出 D 12000 XF-003
2014-01-02 支出 B 1000 XF-004
2014-01-03 支出 E 500 XF-005
.......2014-01-10 收入 A 20000 XS -002
2014-01-10 支出 C 3000 XF-020
......要求结果是:
日期 凭证号 类别 项目 收入 支出 余额
‘’ '上期结转' ‘’ ‘’ 0 0 23000
2014 -01-01 XS-001 收入 A 40000 0 63000
2014-01-01 XF-001 支出 B 2000 61000
2014-01-01 XF-002 支出 C 5000 56000
2014-01-02 XF-003 支出 D 12000 44000
..... 10800
2014-01-10 XS-002 收入 A 20000 0 30800
2014-01-10 XF--020 支出 C 5000 25800
。。
‘小计’ 60000 80000 3000
if OBJECT_ID('u02') is not null
drop table u02create table u02
(日期 varchar(50),类别 varchar(20),项目 varchar(10),金额 int,凭证号 varchar(50))
insert into u02
select '2014-01-01','收入','A','40000','XS-001' union all
select '2014-01-01','支出','B','2000','XF-001' union all
select '2014-01-01','支出','C','5000','XF-002' union all
select '2014-01-02','支出','D','12000','XF-003' union all
select '2014-01-02','支出','B','1000','XF-004' go
with t as
(select 日期,凭证号,类别,项目
,(case 类别 when '收入' then 金额 else 0 end) 收入
,(case 类别 when '支出' then 金额 else 0 end) 支出
,((case 类别 when '收入' then 金额 else 0 end) - (case 类别 when '支出' then 金额 else 0 end)) 余额
from u02)
select * from t
union all
select '小计','','','',SUM(收入),SUM(支出),SUM(余额) from t
/*
----------------结果-------------------
日期 凭证号 类别 项目 收入 支出 余额
2014-01-01 XS-001 收入 A 40000 0 40000
2014-01-01 XF-001 支出 B 0 2000 -2000
2014-01-01 XF-002 支出 C 0 5000 -5000
2014-01-02 XF-003 支出 D 0 12000 -12000
2014-01-02 XF-004 支出 B 0 1000 -1000
小计 40000 20000 20000
*/
-- 没有你的数据,我用syscolumns 做了一个例,你参考一下
-- 上年结转,从哪里取 ???
-- 如果有测试数据,也可以发上来,给你写一下。select 0 , '上年结转' , '' , 100 -- from 你的期初数
union all
select id , cast(colid as varchar(20)), name , colorder from syscolumns
union all
select id, '小计' , '', SUM(colorder) from syscolumns group by id
order by 1 ,2
from q)
select '' as 日期,'上期结转' as 凭证号,'' as 类别,'' as 项目,0 as 收入,0 as 支出,23000 as 余额
union all
select 日期,凭证号,类别,项目,
收入=(case 类别 when '收入' then iamt else 0 end),
支出=(case 类别 when '支出' then iamt else 0 end),
(select sum(case when 类别='支出' then -金额 else 金额 end)+23000 from t b where b.id<=a.id)
from t a
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-26 16:43:05
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([日期] varchar(10),[类别] varchar(4),[项目] varchar(1),[金额] int,[凭证号] varchar(6))
insert [tb]
select '2014-01-01','收入','A',40000,'XS-001' union all
select '2014-01-01','支出','B',2000,'XF-001' union all
select '2014-01-01','支出','C',5000,'XF-002' union all
select '2014-01-02','支出','D',12000,'XF-003' union all
select '2014-01-02','支出','B',1000,'XF-004' union all
select '2014-01-03','支出','E',500,'XF-005' union all
select '2014-01-10','收入','A',20000,'XS-002' union all
select '2014-01-10','支出','C',3000,'XF-020'
--------------开始查询--------------------------
;with f as
(
select 0 as id,'' as 日期,'' as 类别,'' as 项目,23000 as 金额,'上期结转' as 凭证号
union all
select id=row_number()over(order by getdate()),* from tb
)
select
日期, 凭证号,类别,项目,
case when 类别='收入' then 金额 else 0 end as 收入,
case when 类别='支出' then 金额 else 0 end as 支出,
(select sum(case when 类别='收入' or isnull(类别,'')='' then 金额 when 类别='支出' then -金额 else 0 end) from f where id<=t.id) as 余额
from
f as t
----------------结果----------------------------
/* 日期 凭证号 类别 项目 收入 支出 余额
---------- -------- ---- ---- ----------- ----------- -----------
上期结转 0 0 23000
2014-01-01 XS-001 收入 A 40000 0 63000
2014-01-01 XF-001 支出 B 0 2000 61000
2014-01-01 XF-002 支出 C 0 5000 56000
2014-01-02 XF-003 支出 D 0 12000 44000
2014-01-02 XF-004 支出 B 0 1000 43000
2014-01-03 XF-005 支出 E 0 500 42500
2014-01-10 XS-002 收入 A 20000 0 62500
2014-01-10 XF-020 支出 C 0 3000 59500(9 行受影响)*/
小计自己加上去吧 UNION ALL一下就可以了
不过,晕的是SQL2000测试不出来,提示:‘row_number'不是可以识别的函数名,,,晕死
select identity(int,1,1) as id,*
into #tt
from t
select * from #tt
select '' as 日期,'上期结转' as 凭证号,'' as 类别,'' as 项目,0 as 收入,0 as 支出,23000 as 余额
union all
select 日期,凭证号,类别,项目,
收入=(case 类别 when '收入' then 金额 else 0 end),
支出=(case 类别 when '支出' then 金额 else 0 end),
(select sum(case when 类别='支出' then -金额 else 金额 end)+23000 from #tt b where b.id<=a.id)
from #tt a
drop table #tt