这个根据列动态查询如何解?

[redboy999]

有这样的表
编号    名字     备注
51     四川省
510100 成都市     
510104 锦江区
510105 青羊区
510106 金牛区
想添加备注信息如下,

编号    名字     备注
51     四川省
510100 成都市   四川省成都市
510104 锦江区   四川省锦江区
510105 青羊区   四川省青羊区
510106 金牛区   四川省金牛区

其他省市以此类推

[fpzgm]

--> 测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
GO
create table #TB (编号 int,名字 varchar(6),备注 sql_variant)
insert into #TB
select 51,'四川省',null union all
select 510100,'成都市',null union all
select 510104,'锦江区',null union all
select 510105,'青羊区',null union all
select 510106,'金牛区',null

update a
set 备注=b.名字+a.名字
from #tb a,#tb b
where b.编号=left(a.编号,2) and len(a.编号)>2



select * from #TB
编号          名字     备注
-----------------------------------------
51          四川省    NULL
510100      成都市    四川省成都市
510104      锦江区    四川省锦江区
510105      青羊区    四川省青羊区
510106      金牛区    四川省金牛区

(5 行受影响)

[闹铃]

#3 这个妙

update a
set 备注=b.名字+a.名字
from #tb a,#tb b
where b.编号=left(a.编号,2) and len(a.编号)>2

[SQL2088]

--> 测试数据: #TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
GO
create table #TB (编号 int,名字 varchar(6),备注 sql_variant)
insert into #TB
select 51,'四川省',null union all
select 510100,'成都市',null union all
select 510104,'锦江区',null union all
select 510105,'青羊区',null union all
select 510106,'金牛区',null

-- 假设省份编号为2位，且为市的前2位

update a
set 备注=b.名字+a.名字
from #tb a,#tb b
where len(b.编号)=2 and len(a.编号)!=2
  and b.编号=left(a.编号,2)


select * from #TB
编号          名字     备注
----------- ------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
51          四川省    NULL
510100      成都市    四川省成都市
510104      锦江区    四川省锦江区
510105      青羊区    四川省青羊区
510106      金牛区    四川省金牛区

(5 行受影响)

[kevn]

select a.*,备注=(isnull(b.名字,'')+a.名字)from #tb a left join #tb b on left(a.编号,2)= b.编号 and len(a.编号) <> 2

[redboy999]

我用like '%'的形式解决了,和使用left意思一样
谢谢大家了