以上格式发得不好。重新发一次
表
编号 月 日 借方 贷方
hzx 11 1 100
hzx 11 2 200
hzx 11 3 500
hzx 11 4 700
hzx 12 5 900
hzx 12 6 400
略以上是原始表,不能更改的,因为这是别人的C/S的系统。现在我需要做外挂程序,生成以下表。通过条件查询比如11月1号到4号。编号 月 日 借方 贷方 余额
hzx 11 1 100 -100
hzx 11 2 200 +100
hzx 11 3 500 -400
hzx 11 4 700 +300
总计 600 900 +300
表
编号 月 日 借方 贷方
hzx 11 1 100
hzx 11 2 200
hzx 11 3 500
hzx 11 4 700
hzx 12 5 900
hzx 12 6 400
略以上是原始表,不能更改的,因为这是别人的C/S的系统。现在我需要做外挂程序,生成以下表。通过条件查询比如11月1号到4号。编号 月 日 借方 贷方 余额
hzx 11 1 100 -100
hzx 11 2 200 +100
hzx 11 3 500 -400
hzx 11 4 700 +300
总计 600 900 +300
from 表
where 编号=a.编号 and (月=a.月 and 日<=a.日) or 月<a.月)
from 表 a
from 表
where 编号=a.编号 and (月=a.月 and 日<=a.日) or 月<a.月)
from 表 a
order by 编号,月,日
CREATE TABLE [表] (
[编号] [int] NOT NULL ,
[年] [int] NULL ,
[月] [int] NULL ,
[日] [int] NULL ,
[借方] [money] NULL ,
[贷方] [money] NULL
) ON [PRIMARY]
GO
原始数据
表
编号 年 月 日 借方 贷方
hzx 2003 12 8 100
hzx 2004 2 7 100
hzx 2004 11 1 100
hzx 2004 11 1 200
hzx 2004 11 2 500
hzx 2004 11 8 700
dih 2004 12 5 900
hzx 2004 12 6 400
hzx 2005 12 9 600
略以上是原始表,不能更改的,因为这是别人的C/S的系统。现在我需要做外挂程序,生成以下表。
通过条件查询比如2004年11月1号到12月7号。查询编号为hzx的记录编号 年 月 日 借方 贷方 余额
hzx 2004 11 1 100 -300
hzx 2004 11 1 200 -100
hzx 2004 11 2 500 -600
hzx 2004 11 8 700 +100
hzx 2004 12 6 400 -300
总计 1000 900请大侠们测试出以下答案。感谢。
余额是历史至今的余额,不是指查询出的那些记录的结果。
编号 年 月 日 借方 贷方
hzx 2003 12 8 100
hzx 2004 2 7 100在这里已经-200了。通过条件查询比如2004年11月1号到12月7号。查询编号为hzx的记录
这是我们的查询条件(只是一个例子)多谢研究。谢谢
然后循环一条条读取更新余额。
用SQL好像有点麻烦呀!
create table table9(id1 int,year1 int,month1 int,date1 int,in1 money,out1 money)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2003,12,8,100,0)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,2,7,100,0)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,11,1,100,0)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,11,2,0,500)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,12,9,0,900)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,12,12,400,0)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,12,20,0,800)
insert into table9(id1,year1,month1,date1,in1,out1) values(23,2004,12,20,0,800)
if exists(select * from sysobjects where name = 'table10') drop table table10
create table table10(id2 int,year2 int,month2 int,date2 int,in2 money,out2 money,remain money)
declare @id int
declare @year int
declare @month int
declare @date int
declare @in money
declare @out money
declare @remain money
declare detail cursor for select id1,year1,month1,date1,in1,out1 from table9
open detail
fetch next from detail into @id,@year,@month,@date,@in,@out
while @@fetch_status = 0
begin
set @id=22
set @remain = (select sum(isnull(in1,0)-isnull(out1,0)) from table9
where (year1<@year) or (year1=@year and month1<@month) or (year1=@year and month1=@month and date1<=@date) and id1=@id)
insert into table10
select @id,@year,@month,@date,@in,@out,@remain
fetch next from detail into @id,@year,@month,@date,@in,@out
end
close detail
deallocate detailselect * from table9
select * from table10
多谢jzjz
能否用此结构来写出语句
CREATE TABLE [表] (
[编号] [int] NOT NULL ,
[年] [int] NULL ,
[月] [int] NULL ,
[日] [int] NULL ,
[借方] [money] NULL ,
[贷方] [money] NULL
) ON [PRIMARY]
GO
方便大家测试研究。:)感谢。。我也在写,但写不好。
create table table9(id1 int,year1 int,month1 int,date1 int,in1 money,out1 money,remain money)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2003,12,8,100,0)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,2,7,100,0)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,11,1,100,0)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,11,2,0,500)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,12,9,0,900)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,12,12,400,0)
insert into table9(id1,year1,month1,date1,in1,out1) values(22,2004,12,20,0,800)
insert into table9(id1,year1,month1,date1,in1,out1) values(23,2004,12,20,0,800)
--if exists(select * from sysobjects where name = 'table10') drop table table10
--create table table10(id2 int,year2 int,month2 int,date2 int,in2 money,out2 money,remain money)
declare @id int
declare @year int
declare @month int
declare @date int
declare @in money
declare @out money
declare @remain money
declare detail cursor for select id1,year1,month1,date1,in1,out1 from table9
open detail
fetch next from detail into @id,@year,@month,@date,@in,@out
while @@fetch_status = 0
begin
set @id=22
set @remain = (select sum(isnull(in1,0)-isnull(out1,0)) from table9
where (year1<@year) or (year1=@year and month1<@month) or (year1=@year and month1=@month and date1<=@date) and id1=@id)
update table9
set remain=@remain
where id1=@id and year1=@year and month1=@month and date1=@date and in1=@in and out1=@out
-- insert into table10
-- select @id,@year,@month,@date,@in,@out,@remain
fetch next from detail into @id,@year,@month,@date,@in,@out
end
close detail
deallocate detailselect * from table9
--select * from table10不过最好用时间来区分而不要用日期来区分
以上 未经测试 你试试吧
hzx 2004 11 1 200當如果失去了Primary key ,單純的一條sql 語句是沒有辦法實現的
drop table ks
go
Create Table ks(
mcode varchar(10) null,
myear int null,
mMonth int null,
mday int null,
mout int null,
minput int null
)
Create Table #ks(
mcode varchar(10) null,
myear varchar(4) null,
mMonth varchar(2) null,
mday varchar(2) null,
mout varchar(8) null,
minput varchar(8) null,
pp varchar(8) null
)
insert into ks values('hzx',2003,12,8,100,0)
insert into ks values('hzx',2004,2,7,100,0)
insert into ks values('hzx',2004,11,1,100,0)
insert into ks values('hzx',2004,11,1,0,200)
insert into ks values('hzx',2004,11,2,500,0)
insert into ks values('hzx',2004,11,8,0,700)
insert into ks values('dih',2004,12,5,0,900)
insert into ks values('hzx',2004,12,6,400,0)
insert into ks values('hzx',2004,12,9,600,0)
Declare @Mcode varchar(10),
@myear int,
@mMonth int,
@mDay int,
@mOut int,
@mInput int
Declare @sum int,@total int,@subtotal int
set @total=0
set @subtotal=0
set @sum=0
select @sum=sum(mInput-mout) from ks where mcode='hzx' and cast(cast(myear as varchar(4))+'/'+cast(mmonth as varchar(2))+'/'+cast(mday as varchar(2)) as datetime )<'2004/11/01'
Declare kk cursor for
select mcode,myear,mMonth,mDay,mOut,mInput From Ks where mcode='hzx'
and cast(cast(myear as varchar(4))+'/'+cast(mmonth as varchar(2))+'/'+cast(mday as varchar(2)) as datetime ) >='2004/11/01'
and cast(cast(myear as varchar(4))+'/'+cast(mmonth as varchar(2))+'/'+cast(mday as varchar(2)) as datetime )<='2004/12/07'
order by mcode,myear,mmonth,mday
open kk
fetch next from kk into @mcode,@myear,@mMonth,@mday,@mOut,@mInput
while @@fetch_status=0
begin
set @sum=@sum-@mout+@minput
set @total=@total+@mout
set @subtotal=@subtotal+@mInput
insert into #ks
select @mcode,@myear,@mMonth,@mday,@mOut,@mInput ,@sum
fetch next from kk into @mcode,@myear,@mMonth,@mday,@mOut,@mInput
end
close kk
deallocate kk
insert into #ks
Select 'hzx','','','',@total,@subtotal,''
select * from #ks
drop table #ks
建立倆個過程
1. 統計日期前面餘額
2. 利用Cursor 一條條記錄統計餘額生成結果表