有一个数据库表有4个字段,第一个字段是ID,第二、三、四都是字符字段如:
ID str1 str2 str3
1 Z L M
2 L M N
3 L M N
4 L M N
.....
我在程序中显示的时候,准备加一个字段,这样来进行显示
ID str1 str2 str3 addstr
1 Z L M
2 L M N
3 L M N
4 L M N
5 N N L ZLMLMNLMNLMNNNL
6 L N O LMNLMNLMNNNLLNO
.....
也就是说,新添加的字段的内容就是前5条(包括自己本身的那条)记录1,2,3字段的合并显示,各位大侠想个招。
ID str1 str2 str3
1 Z L M
2 L M N
3 L M N
4 L M N
.....
我在程序中显示的时候,准备加一个字段,这样来进行显示
ID str1 str2 str3 addstr
1 Z L M
2 L M N
3 L M N
4 L M N
5 N N L ZLMLMNLMNLMNNNL
6 L N O LMNLMNLMNNNLLNO
.....
也就是说,新添加的字段的内容就是前5条(包括自己本身的那条)记录1,2,3字段的合并显示,各位大侠想个招。
drop table testTable create table testTable
(id int primary key identity(1,1),
str1 varchar(1),
str2 varchar(1),
str3 varchar(1),
addstr varchar(100)
)insert into testTable values('Z','L','M','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','T','')
go
alter procedure mytestProcedure
@FCurrentid int
asdeclare
@FieldStr varchar(100),
@MaxId int,
@CurrentID intset @Fieldstr = ''
set @CurrentID = @FCurrentid + 1
select @Maxid = max(id) from testtable
select @FieldStr = @FieldStr + str1 +str2 +str3 from testtable where id <= @FCurrentidupdate testtable set addstr = @FieldStr where id = @FCurrentid
while @CurrentID < @MaxID +1
begin
select @FieldStr = right(@FieldStr,len(@FieldStr)-3*len(str1)) + str1+str2+str3 from testtable where id = @CurrentID
update testtable set addstr = @FieldStr where id = @CurrentID
set @CurrentID = @CurrentID + 1
end
gogoexec mytestprocedure 5select * from testtable
------------------
1 Z L M
2 L M N
3 L M N
4 L M N
5 L M N ZLMLMNLMNLMNLMN
6 L M N LMNLMNLMNLMNLMN
7 L M N LMNLMNLMNLMNLMN
8 L M N LMNLMNLMNLMNLMN
9 L M T LMNLMNLMNLMNLMT
应该还有更简单的方式可以一句话搞定,但我暂时没想到。
if exists(select 1 from sysobjects where id = object_id(N'testTable'))
drop table testTable create table testTable
(id int primary key identity(1,1),
str1 varchar(1),
str2 varchar(1),
str3 varchar(1),
addstr varchar(100)
)insert into testTable values('Z','L','M','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','N','')
insert into testTable values('L','M','T','')
goif exists(select 1 from sysobjects where id = object_id(N'myfunction'))
drop function myfunction
go--自定义函数,根据传递的创始化id(就是从第几行开始),和当前id来返回一个当前id前面几行的一个字符串;
create function myfunction (@FinitID int,@FCurrID int)
returns varchar(100)
as
begin
declare
@FieldStr varchar(100)set @Fieldstr = ''
select @FieldStr = @FieldStr + str1 +str2 +str3
from testtable
where id between (@FCurrID - @FinitID +1) and @FCurrIDreturn(@FieldStr) endgo
update testtable set addstr = test.dbo.myfunction(5,id) from testtable where id >4
select * from testtable
RETURNS NVARCHAR(4000) AS
BEGIN
Declare @SQL NVARCHAR(4000)
Declare @ROW INT
Select @SQL = ''
Select @ROW = COUNT(1) FROM (Select Top 5 * from TADD Where ID <= @ID Order By ID Desc) T
IF @ROW = 5
Begin
Select @SQL = @SQL + Str From (Select Top 5 Str1+Str2+Str3 Str from (Select Top 5 * from TADD Where ID <= @ID Order By ID Desc ) T Order By ID ) TT
End
Return @SQL
END-----------------------------Select ID, STR1, STR2, STR3, DBO.AddAtr(ID) ADDSTR FROM TADD