27,580
社区成员
发帖
与我相关
我的任务
分享
use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
[name] varchar(20),
[no] varchar(5)
)
go
--插入测试数据
insert into tb select '九江市','01'
union all select '市辖区','0101'
union all select '彭泽县','0102'
union all select '南昌市','02'
union all select '市辖区','0201'
union all select '南昌县','0202'
union all select '萍乡市','03'
union all select '市辖区','0301'
union all select '平乡县','0302'
go
--代码实现
update a set [name]=isnull(b.[name]+a.[name],a.[name])
from tb a left join tb b
on left(a.[no],2)=b.[no] and a.[no]<>b.[no]
select * from tb
/*测试结果
name no
---------------------
九江市 01
九江市市辖区 0101
九江市彭泽县 0102
南昌市 02
南昌市市辖区 0201
南昌市南昌县 0202
萍乡市 03
萍乡市市辖区 0301
萍乡市平乡县 0302
(9行受影响)
*/
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([name] varchar(15),[no] varchar(4))
insert [TB]
select '九江市','01' union all
select '市辖区','0101' union all
select '彭泽县','0102' union all
select '南昌市','02' union all
select '市辖区','0201' union all
select '南昌县','0202' union all
select '萍乡市','03' union all
select '市辖区','0301' union all
select '平乡县','0302'
select * from [TB]
UPDATE dbo.TB
SET TB.NAME = T.NAME
FROM (
SELECT (SELECT NAME FROM TB WHERE LEFT(A.no,2) = no)+NAME AS NAME,A.no FROM TB A
WHERE LEN(no)>2)T
WHERE TB.no = T.NO
/*name no
--------------- ----
九江市 01
九江市市辖区 0101
九江市彭泽县 0102
南昌市 02
南昌市市辖区 0201
南昌市南昌县 0202
萍乡市 03
萍乡市市辖区 0301
萍乡市平乡县 0302
(9 行受影响)
*/
update t2 set [name] = isnull([name],'')+t1.[name]
from t1 left join t2
on left(t1.[no],2) = t2.[no]
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (name varchar(16),no varchar(4))
insert into #tb
select '九江市','01' union all
select '市辖区','0101' union all
select '彭泽县','0102' union all
select '南昌市','02' union all
select '市辖区','0201' union all
select '南昌县','0202' union all
select '萍乡市','03' union all
select '市辖区','0301' union all
select '平乡县','0302'
update a set name =b.name+a.name
from #tb a,#tb b
where len(a.no)>2 and len(b.no)=2
and b.no=left(a.no,2)
select * from #tb
name no
---------------- ----
九江市 01
九江市市辖区 0101
九江市彭泽县 0102
南昌市 02
南昌市市辖区 0201
南昌市南昌县 0202
萍乡市 03
萍乡市市辖区 0301
萍乡市平乡县 0302
(9 行受影响)
SELECT ISNULL(T2.NAME,'')+T1.NAME AS NAME,T1.NO
FROM TB T1
LEFT JOIN TB T2 ON LEFT(T1.NO)=T2.NO
ORDER BY ISNULL(T2.NAME,'')+T1.NAME