select *,prebal=(select top 1 curbal from tablename where number=a.number and date1<a.date1 order by date1 desc ) from tablename a
declare @t table(number int,date1 char(6),curbal int) insert into @t select 1, '200502', 333 union select 2, '200306', 555 union select 2, '200509', 222 union select 2 ,'200501', 444 union select 3 ,'200102', 999 union select 1 ,'200101', 777 union select 3 ,'200403', 000 select *,prebal=isnull((select top 1 curbal from @t where number=a.number and date1<a.date1 order by date1 desc ),curbal) from @t a /* number date1 curbal prebal ------------------------------------- 1 200101 777 777 1 200502 333 777 2 200306 555 555 2 200501 444 555 2 200509 222 444 3 200102 999 999 3 200403 0 999*/
--数据类型未说明。curbal改为字符型测试。 declare @t table(number int,date1 char(6),curbal char(3)) insert into @t select 1, '200502', '333' union select 2, '200306', '555' union select 2, '200509', '222' union select 2 ,'200501', '444' union select 3 ,'200102', '999' union select 1 ,'200101', '777' union select 3 ,'200403', '000' select *,prebal=isnull((select top 1 curbal from @t where number=a.number and date1<a.date1 order by date1 desc ),curbal) from @t a /* number date1 curbal prebal ------------------------------------- 1 200101 777 777 1 200502 333 777 2 200306 555 555 2 200501 444 555 2 200509 222 444 3 200102 999 999 3 200403 000 999*/
select * ,(select top 1 curbal from 表 where number=t.number and [date]<>t.[date] order by [date] desc )as 'prebal' from 表 t
--测试数据 create table test(number int,date1 varchar(20),curbal int) insert into test select 1,'200502',333 union all select 2,'200306',555 union all select 2,'200509',222 union all select 2,'200501',444 union all select 3,'200102',999 union all select 1,'200101',777 union all select 3,'200403',000--函数 create function test_t(@shijian varchar(20)) returns int as begin declare @min int declare @value int declare @date varchar(20) set @min=(select cast(min(date1) as int) from test) if @shijian=@min begin set @value=0 end else if @shijian<>@min begin set @date=(select max(date1) from test where date1<@shijian) set @value=(select curbal from test where date1=@date)+(select curbal from test where date1=@shijian) end return(@value) end --查询 select number,date1,curbal,dbo.test_t(date1) from test
有点错误,将函数的第11行改成:set @value=(select curbal from test where date1=@shijian)
from tablename a
insert into @t
select 1, '200502', 333 union
select 2, '200306', 555 union
select 2, '200509', 222 union
select 2 ,'200501', 444 union
select 3 ,'200102', 999 union
select 1 ,'200101', 777 union
select 3 ,'200403', 000 select *,prebal=isnull((select top 1 curbal from @t where number=a.number and date1<a.date1 order by date1 desc ),curbal)
from @t a
/*
number date1 curbal prebal
-------------------------------------
1 200101 777 777
1 200502 333 777
2 200306 555 555
2 200501 444 555
2 200509 222 444
3 200102 999 999
3 200403 0 999*/
declare @t table(number int,date1 char(6),curbal char(3))
insert into @t
select 1, '200502', '333' union
select 2, '200306', '555' union
select 2, '200509', '222' union
select 2 ,'200501', '444' union
select 3 ,'200102', '999' union
select 1 ,'200101', '777' union
select 3 ,'200403', '000'
select *,prebal=isnull((select top 1 curbal from @t where number=a.number and date1<a.date1 order by date1 desc ),curbal)
from @t a
/*
number date1 curbal prebal
-------------------------------------
1 200101 777 777
1 200502 333 777
2 200306 555 555
2 200501 444 555
2 200509 222 444
3 200102 999 999
3 200403 000 999*/
,(select top 1 curbal
from 表
where number=t.number
and [date]<>t.[date]
order by [date] desc
)as 'prebal'
from 表 t
create table test(number int,date1 varchar(20),curbal int)
insert into test
select 1,'200502',333
union all
select 2,'200306',555
union all
select 2,'200509',222
union all
select 2,'200501',444
union all
select 3,'200102',999
union all
select 1,'200101',777
union all
select 3,'200403',000--函数
create function test_t(@shijian varchar(20))
returns int
as
begin
declare @min int
declare @value int
declare @date varchar(20)
set @min=(select cast(min(date1) as int) from test)
if @shijian=@min
begin
set @value=0
end
else if @shijian<>@min
begin
set @date=(select max(date1) from test where date1<@shijian)
set @value=(select curbal from test where date1=@date)+(select curbal from test where date1=@shijian)
end
return(@value)
end
--查询
select number,date1,curbal,dbo.test_t(date1) from test