我有两张表 NameLetter(SerialID,name,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z)
NameIndex(SerialID,name,Letter)
其中NameLetter中的Name字段是存放单个字符(即NameIndex表中Name的首字母) NameIndex 中的Name存放字符串
现在如何能将NameIndex中的除去首字母的剩余字串以剩余字串首字母为字段更新到对应字段中?
例如: nameIndex 表中name='zsl' 先将首字母'z' 插入到NameLetter的Name字段上,然后再根据剩余字串'sl'更新到NameLetter表中Name='Z' 的动态s字段中?
insert into NameLetter(Name) select distinct left(Name,1) from NameIndex where Name not in (select Name from NameLetter ) update NameLetter set ? 如何写?
NameIndex(SerialID,name,Letter)
其中NameLetter中的Name字段是存放单个字符(即NameIndex表中Name的首字母) NameIndex 中的Name存放字符串
现在如何能将NameIndex中的除去首字母的剩余字串以剩余字串首字母为字段更新到对应字段中?
例如: nameIndex 表中name='zsl' 先将首字母'z' 插入到NameLetter的Name字段上,然后再根据剩余字串'sl'更新到NameLetter表中Name='Z' 的动态s字段中?
insert into NameLetter(Name) select distinct left(Name,1) from NameIndex where Name not in (select Name from NameLetter ) update NameLetter set ? 如何写?
--这样??
declare @sql nvarchar(2000) ,@i int
select @sql ='' ,@i =1
while @i<(select max([SerialID]) from NameIndex)
begin
select @sql = ' update NameLetter set s = '+right([Name],len([Name])-1)
+' where [SerialID] = '+[SerialID] +' and [Name]='''+left([Name],1)+ ''' '
from NameIndex where [SerialID] = @i
exec(@sql)
set @i = @i +1
end
把大师的改动下:--测试数据
create table NameLetter(SerialID int,[Name] varchar(20),a varchar(20),b varchar(20))
insert into NameLetter(SerialID,[Name]) values(1,'a')
insert into Nameletter(SerialID,[Name]) values(2,'b')
declare @NameIndex table(SerialID int,[Name] varchar(20),Letter varchar(20))
insert into @NameIndex
select 1,'abc','tt' union all
select 2,'babbccc','tt'
--select * from @NameLetter
--select * from @NameIndex
declare @sql nvarchar(4000),@i int
select @sql = '',@i =1
while @i<=(select max(SerialID) from @NameIndex)
begin
select @sql = 'update NameLetter set '+substring([Name],2,1)+' = '''+right([Name],len([Name])-1)
+''' where SerialID = '+cast(SerialID as varchar)+' and [Name]='''+left([Name],1)+''''
from @NameIndex where SerialID = @i
print @sql
exec(@sql)
set @i = @i+1
end