bank表
bankAccount bankName
001 AAA
002 BBB
003 CCC
balance 表 (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_acc
billData表（明细单据表，如果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 code
if object_id('bank') is not null drop table bankif object_id('balance') is not null drop table balance if object_id('billData') is not null drop table billDatacreate 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]gocreate 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)goinsert into bank select '001','AAA' union all select '002','BBB' union all select '003','CCC'goinsert into balance select 2012,4,1000000,10000,20000,5000,4000goinsert into billData select '2012-05-23','收','abc','001','001',100,0union all select '2012-05-23','付','bcd','002','001',100,0union all select '2012-05-24','收','ccc','002','001',200,0union all select '2012-05-25','收','abc','002','001',100,0goif object_id('Balance_YM','P') is not null drop proc Balance_YMgocreate proc Balance_YM@year int=2012,@month int=5,@day int=24asselect 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 [email protected] and [email protected]) or (@month=1 and [email protected] and iMonth=12)if @@rowcount>0begin 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)[email protected] and datepart(month,billDate)[email protected] and datepart(day,billDate)<@day )t select * from #tenddrop table #tgoexec Balance_YM 2012,5,24exec Balance_YM 2012,5,25go
------解决方案--------------------