bank表
bankAccount bankName
001 AAA
002 BBB
003 CCCbalance 表 (balance为月度表,pre_acc为期初余额,deaIn_acc为调入金额,deaOu_acc为调出金额,in_acc为本期收入,ou_acc为本期支出,las_acc为期末余额las_acc=pre+deaIn_acc-deaOu_acc+in_acc-ou_acc)
iYear iMonth pre_acc deaIn_acc deaOu_acc in_acc ou_acc Las_accbillData表(明细单据表,如果otherBankAccount的编码存在bank表中,为调拨发生额计入deaIn_acc,deaOu_acc)
billDate billType clientName bankAccount otherBankAccount moneyLow bewrite
2012-05-23 收 abc 001 100
2012-05-23 付 bcd 002 100
2012-05-24 收 ccc 002 200
2012-05-25 收 abc 002 001 100求计算出24号和25号的balance表的详细数据 pre_acc 为期初+前一天的的数据的余额另,烦劳各位师兄给看看这么设计数据的结构是否合理?请给出一定的意见
bankAccount bankName
001 AAA
002 BBB
003 CCCbalance 表 (balance为月度表,pre_acc为期初余额,deaIn_acc为调入金额,deaOu_acc为调出金额,in_acc为本期收入,ou_acc为本期支出,las_acc为期末余额las_acc=pre+deaIn_acc-deaOu_acc+in_acc-ou_acc)
iYear iMonth pre_acc deaIn_acc deaOu_acc in_acc ou_acc Las_accbillData表(明细单据表,如果otherBankAccount的编码存在bank表中,为调拨发生额计入deaIn_acc,deaOu_acc)
billDate billType clientName bankAccount otherBankAccount moneyLow bewrite
2012-05-23 收 abc 001 100
2012-05-23 付 bcd 002 100
2012-05-24 收 ccc 002 200
2012-05-25 收 abc 002 001 100求计算出24号和25号的balance表的详细数据 pre_acc 为期初+前一天的的数据的余额另,烦劳各位师兄给看看这么设计数据的结构是否合理?请给出一定的意见
解决方案 »
- sql语句写case when 请问如何写?如图
- SQL server 2008创建DB时如何设置Collation才能适应多国语系统
- 问个查询语句
- 怎么将char转换成MONEY各位高手帮帮小弟~
- 数据类型varchar 转换为bigint时出错。请教大家。
- sql2000能否将2005的数据库文件导进去
- 为什么我的SQL语句里的字符串内容不区分大小写呀
- 请问SQLSERVER中备份文件和备份到设备有什么不同?备份是为什么要新建备份设备,有什么用?
- 有个问题,请帮忙。谢谢
- 开SQL SERVER 开发的网站,如何把那库文件放上服务器?
- sqlserver2008的连接问题(maximum number of '1' )
- 无法在 Unicode 和非 Unicode 字符串之间转换
if object_id('bank') is not null drop table bank
if object_id('balance') is not null drop table balance
if object_id('billData') is not null drop table billData
create table bank(
bankAccount nvarchar(10) not null primary key,
bankName nvarchar(50) null
)
create table balance(
iYear int not null,
iMonth int not null,
pre_acc float not null,
deaIn_acc float not null,
deaOu_acc float not null,
in_acc float not null,
ou_acc float not null,
Las_acc as(pre_acc+deaIn_acc-deaOu_acc+in_acc-ou_acc)
)
alter table balance add constraint [PK_balance] primary key clustered(iYear,iMonth) on [PRIMARY]
go
create table billData(
id int identity(1,1) not null primary key,
billDate datetime not null,
billType nvarchar(1) not null default('收'),
clientName nvarchar(20) not null,
bankAccount nvarchar(10) not null,
otherBankAccount nvarchar(10) null,
moneyLow float not null,
bewrite bit
)
go
insert into bank
select '001','AAA'
union all select '002','BBB'
union all select '003','CCC'
go
insert into balance
select 2012,4,1000000,10000,20000,5000,4000
go
insert into billData
select '2012-05-23','收','abc','001','001',100,0
union all select '2012-05-23','付','bcd','002','001',100,0
union all select '2012-05-24','收','ccc','002','001',200,0
union all select '2012-05-25','收','abc','002','001',100,0
go
if object_id('Balance_YM','P') is not null drop proc Balance_YM
go
create proc Balance_YM
@year int=2012,
@month int=5,
@day int=24
as
select iYear,iMonth,pre_acc,deaIn_acc=0,deaOu_acc=0,
in_acc=0,ou_acc=0,Las_acc=0
into #t
from balance
where (@month<>1 and iYear=@year and iMonth=@month-1)
or (@month=1 and iYear=@year-1 and iMonth=12)
if @@rowcount>0
begin
update #t
set pre_acc=#t.pre_acc+t.in_acc-t.ou_acc,
in_acc=t.in_acc,
ou_acc=t.ou_acc
from (
select in_acc=isnull(sum(case when billType='收' then moneyLow else 0 end),0),
ou_acc=isnull(sum(case when billType='付' then moneyLow else 0 end),0)
from billData
where datepart(year,billDate)=@year and datepart(month,billDate)=@month
and datepart(day,billDate)<@day
)t
select * from #t
end
drop table #t
go
exec Balance_YM 2012,5,24
exec Balance_YM 2012,5,25
go
1.Acc应是Account的简写,也就是科目,像你的pre_acc deaIn_acc deaOu_acc in_acc ou_acc Las_acc
列改为pre_bal deaIn_bal deaOu_bal in_bal ou_bal Las_bal可能会好些
2.根据你的表调出与调入金额不知从何处体现???
--应该是这样才对,犯了个基本错误,上月期末应为本月期初
if object_id('Balance_YM','P') is not null drop proc Balance_YM
go
create proc Balance_YM
@year int=2012,
@month int=5,
@day int=24
as
select iYear,iMonth,pre_acc=Las_acc,deaIn_acc=0,deaOu_acc=0,
in_acc=0,ou_acc=0,Las_acc=0
into #t
from balance
where (@month<>1 and iYear=@year and iMonth=@month-1)
or (@month=1 and iYear=@year-1 and iMonth=12)
if @@rowcount>0
begin
update #t
set in_acc=t.in_acc,
ou_acc=t.ou_acc
from (
select in_acc=isnull(sum(case when billType='收' then moneyLow else 0 end),0),
ou_acc=isnull(sum(case when billType='付' then moneyLow else 0 end),0)
from billData
where datepart(year,billDate)=@year and datepart(month,billDate)=@month
and datepart(day,billDate)<@day
)t
update #t set Las_acc=pre_acc+deaIn_acc-deaOu_acc+in_acc-ou_acc;
select * from #t
end
drop table #t
go
exec Balance_YM 2012,5,24
exec Balance_YM 2012,5,25
go
你这测试数据给的有问题吧?
这个问题你首先把那几个数字算出来,可能你不会的是如何计算
:pre_acc 为期初+前一天的的数据的余额
这个问题你可以递归:相当于累计求和问题:--> 测试数据:[tbl]
go
if object_id('[tbl]') is not null
drop table [tbl]
go
create table [tbl](
[name] varchar(1),
[date] varchar(5),
[num] int
)
go
insert [tbl]
select 'a','1-1号',1 union all
select 'b','1-2号',4 union all
select 'a','1-3号',8 union all
select 'a','1-4号',5 union all
select 'b','1-5号',6 union all
select 'b','1-6号',9;with t
as(
select ROW_NUMBER()over(partition by name
order by [date]) as id,
*,num as total from tbl
),
m as(
select id,name,[date],num,total from t where id=1
union all
select a.id,a.name,a.[date],a.num,b.total+a.num from t a
inner join m b on a.id=b.id+1 and a.name=b.name
)
select name,[date],num,total from m order by name/*
name date num total
a 1-3号 8 8
a 1-4号 5 13
a 1-1号 1 14
b 1-2号 4 4
b 1-5号 6 10
b 1-6号 9 19
*/
-------------------------------------------------------
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([ID] int,[借方] int,[贷方] int)
insert [tbl]
select 1,10,0 union all
select 2,0,4 union all
select 3,0,2 union all
select 4,1,0SELECT
ID,借方,贷方,
[余额]=(SELECT SUM(借方-贷方) FROM tbl WHERE ID<=a.ID)
FROM tbl AS a
--其实这个问题不用楼上写的那么复杂