--生成测试数据
create table 表A(
GL_No varchar(20),
GL_Name varchar(20))insert into 表A select 'GL001','浙江'
insert into 表A select 'GL002','山东'create table 表B(
AL_No varchar(20),
AL_GL_No varchar(20),
AL_Name varchar(20))insert into 表B select 'GL001_AL001','GL001','大中型城市'
insert into 表B select 'GL002_AL001','GL002','大型城市'create table 表C(
BL_No varchar(20),
BL_AL_No varchar(20),
BL_Name varchar(20))insert into 表C select 'GL001_AL001_BL001','GL001_AL001','杭州'
insert into 表C select 'GL001_AL001_BL002','GL001_AL001','绍兴'create table 表D(
HL_BL_No varchar(20),
BL_Name varchar(20))insert into 表D select 'GL001_AL001_BL002','诸暨'--数据生成
create table t(
ID int identity(1,1),
PID int ,
Caption varchar(20) ,
Code varchar(30) )insert into t(PID,Caption,Code)
select 0,GL_Name,GL_No from 表Ainsert into t(PID,Caption,Code)
select 0,AL_Name,AL_No from 表Binsert into t(PID,Caption,Code)
select 0,BL_Name,BL_No from 表Cinsert into t(PID,Caption,Code)
select 0,BL_Name,HL_BL_No+'000000' from 表Dupdate
a
set
PID = b.ID
from
t a
inner join
t b
on
left(a.Code,len(a.Code)-6) = b.Code
where
len(a.code)>6update t set Code = null where right(Code,6) = '000000'select * from t
--删除测试数据
drop table 表A
drop table 表B
drop table 表C
drop table 表D
drop table t
create table 表A(
GL_No varchar(20),
GL_Name varchar(20))insert into 表A select 'GL001','浙江'
insert into 表A select 'GL002','山东'create table 表B(
AL_No varchar(20),
AL_GL_No varchar(20),
AL_Name varchar(20))insert into 表B select 'GL001_AL001','GL001','大中型城市'
insert into 表B select 'GL002_AL001','GL002','大型城市'create table 表C(
BL_No varchar(20),
BL_AL_No varchar(20),
BL_Name varchar(20))insert into 表C select 'GL001_AL001_BL001','GL001_AL001','杭州'
insert into 表C select 'GL001_AL001_BL002','GL001_AL001','绍兴'create table 表D(
HL_BL_No varchar(20),
BL_Name varchar(20))insert into 表D select 'GL001_AL001_BL002','诸暨'--数据生成
create table t(
ID int identity(1,1),
PID int ,
Caption varchar(20) ,
Code varchar(30) )insert into t(PID,Caption,Code)
select 0,GL_Name,GL_No from 表Ainsert into t(PID,Caption,Code)
select 0,AL_Name,AL_No from 表Binsert into t(PID,Caption,Code)
select 0,BL_Name,BL_No from 表Cinsert into t(PID,Caption,Code)
select 0,BL_Name,HL_BL_No+'000000' from 表Dupdate
a
set
PID = b.ID
from
t a
inner join
t b
on
left(a.Code,len(a.Code)-6) = b.Code
where
len(a.code)>6update t set Code = null where right(Code,6) = '000000'select * from t
--删除测试数据
drop table 表A
drop table 表B
drop table 表C
drop table 表D
drop table t
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货