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

redboy999 2010-10-11 11:29:26

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

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

其他省市以此类推



...全文
58 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
redboy999 2010-10-12
我用like '%'的形式解决了,和使用left意思一样
谢谢大家了
回复
kevn 2010-10-11
select a.*,备注=(isnull(b.名字,'')+a.名字)from #tb a left join #tb b on left(a.编号,2)= b.编号 and len(a.编号) <> 2
回复
SQL2088 2010-10-11
--> 测试数据: #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 行受影响)
回复
闹铃 2010-10-11

#3 这个妙

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

回复
fpzgm 2010-10-11

--> 测试数据: #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 行受影响)
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-11 11:29
社区公告
暂无公告