请教关于街路巷问题的解法

frank_zu 2006-04-19 04:22:40
近日 遇到有关街路巷的问题
具体是
表中有两个字段:code,name,
code :记录编码,char(6),街的编码类似‘__0000’,路的编码类似'____00',巷的编码类似'______'
例如
----------------------------------------------
name code
----------------------------------------------
南京路 '210000'
步行街 (南京路) '211200'
回马巷(南京路步行街) '211201'
-----------------------------------------------
name: 记录了城市街道的名称(名称没有 详细处理,其中括号的内容不存在 )
现在 想处理这个name,让名称具体,比如上面的回马巷 处理后是 南京路步行街回马巷,步行街成为南京路步行街,
请教,最快的处理方法
frank_zu@126.com

...全文
189 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
十一月猪 2006-04-28
  • 打赏
  • 举报
回复
create table t_load
( name varchar(100) , code varchar(1000))

insert into t_load
select '南京路', '210000' union
select '步行街', '211200' union
select '回马巷', '211201'


declare @code varchar(8000)
declare @code_comp varchar(8000)
declare @name varchar(8000)
select @code = ''
select @code_comp = ''
select @name = ''


declare cursor1 scroll cursor for select code , name from t_load
open cursor1

fetch next from cursor1 into @code ,@name

while @@fetch_status = 0
begin
select @code_comp = substring(@code,3,4)
if @code_comp <> '0000'
begin
select @code_comp = substring(@code,5,2)
print @code_comp

select @name = @name +'('+ substring(name,1,3) + ')'
from t_load
where code = substring(@code,1,2) + '0000'
print @name

if @code_comp = '00'

begin

update t_load
set name = @name
where code = @code
end

else

begin
select @name = substring(@name,1,len(@name) - 1) +substring(name,1,3) + ')'
from t_load
where code = substring(@code,1,4) + '00'

update t_load
set name = @name
where code = @code
end

end

fetch next from cursor1 into @code , @name

end

close cursor1
deallocate cursor1

select * from t_load
frank_zu 2006-04-28
  • 打赏
  • 举报
回复
谢谢,流浪猫,
好像 ,我没有表达清楚,我是要把表里的name字段更改,而不是,拼接显示啊
huailairen 2006-04-19
  • 打赏
  • 举报
回复
name
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
南京路
步行街回马巷
南京路步行街回马巷

(所影响的行数为 3 行)
huailairen 2006-04-19
  • 打赏
  • 举报
回复
select [name]
from mytable
where right(code,4)='0000'
union all
select b.name+a.name
from mytable a join mytable b on (substring(a.code,3,2)=substring(b.code,3,2) and right(b.code,2)='00')
where right(a.code,2)<>'00'
union all
select c.name+b.name +a.name
from mytable a join mytable b on (substring(a.code,3,2)=substring(b.code,3,2) and right(b.code,2)='00')
join mytable c on(right(c.code,4)='0000' and substring(a.code,1,2)=substring(c.code,1,2))
where right(a.code,2)<>'00'
huailairen 2006-04-19
  • 打赏
  • 举报
回复
create table mytable(name varchar(100), code varchar(6))
insert into mytable select
'南京路', '210000' union select
'步行街' , '211200' union select
'回马巷', '211201'

select c.name+b.name +a.name
from mytable a join mytable b on (substring(a.code,3,2)=substring(b.code,3,2) and right(b.code,2)='00')
join mytable c on(right(c.code,4)='0000' and substring(a.code,1,2)=substring(c.code,1,2))
where right(a.code,2)<>'00'

结果

-------------------------------------------------------------------------------------
南京路步行街回马巷

(所影响的行数为 1 行)

27,580

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧