table_old
---------
id name
table_new
---------
id name
两个表连接问题、
现有两个表table_old ,table_new
两个表结构完全相同。条件:1 把table_old ,table_new 连接成一个表 table_2009(id,name)
2 id相同的数据记录(),name 取table_new里的值
3 table_old 里专有的ID,在新表里,每条NAME数据前面加上"old"
4 table_old 表里的ID前有S,table_new的没有, table_2009表ID 要加S
例子:
table_old
---------
id name
S001 张
S002 王
S004 孙table_new
---------
id name
001 张
002 李
003 赵出来的结构应该是:
table_2009
---------
id name
S001 张
S002 李
S003 赵
004 old孙
---------
id name
table_new
---------
id name
两个表连接问题、
现有两个表table_old ,table_new
两个表结构完全相同。条件:1 把table_old ,table_new 连接成一个表 table_2009(id,name)
2 id相同的数据记录(),name 取table_new里的值
3 table_old 里专有的ID,在新表里,每条NAME数据前面加上"old"
4 table_old 表里的ID前有S,table_new的没有, table_2009表ID 要加S
例子:
table_old
---------
id name
S001 张
S002 王
S004 孙table_new
---------
id name
001 张
002 李
003 赵出来的结构应该是:
table_2009
---------
id name
S001 张
S002 李
S003 赵
004 old孙
case when b.name is null then replace(a.id,'s') as id,
isnull(b.name,'old'+a.name)as name
from table_old a left join table_new b on a.id='s'+b.id
select
a.id,isnull(b.name,'old'+a.name)as name
from table_old a left join table_new b on a.id='s'+b.id
as
select id=substring(id,2,3),name='old'+name from table_old a where not exists(select top 1 0 from table_new where a.id=id)
union all
select id='S'+id,name from table_new
select
isnull(a.id,'S'+b.id),isnull(b.name,'old'+a.name)as name
from table_old a full outer join table_new b on a.id='s'+b.id
INSERT @TABLE_OLD SELECT 'S001', N'张'
INSERT @TABLE_OLD SELECT 'S002', N'王'
INSERT @TABLE_OLD SELECT 'S004', N'孙'
DECLARE @table_new TABLE( id VARCHAR(10), name NVARCHAR(10))
INSERT @TABLE_NEW SELECT '001', N'张'
INSERT @TABLE_NEW SELECT '002', N'李'
INSERT @TABLE_NEW SELECT '003', N'赵'
SELECT 'S'+ID ID,NAME FROM @TABLE_NEW
UNION ALL
SELECT ID,'OLD'+NAME FROM @TABLE_OLD M WHERE NOT EXISTS(SELECT 1 FROM (SELECT 'S'+ID ID,NAME FROM @TABLE_NEW)N WHERE N.ID=M.ID )
/*ID NAME
----------- -------------
S001 张
S002 李
S003 赵
S004 OLD孙*/
INSERT @TABLE_OLD SELECT 'S001', N'张'
INSERT @TABLE_OLD SELECT 'S002', N'王'
INSERT @TABLE_OLD SELECT 'S004', N'孙'
DECLARE @table_new TABLE( id VARCHAR(10), name NVARCHAR(10))
INSERT @TABLE_NEW SELECT '001', N'张'
INSERT @TABLE_NEW SELECT '002', N'李'
INSERT @TABLE_NEW SELECT '003', N'赵'
select
isnull(a.id,'S'+b.id) as id,isnull(b.name,'old'+a.name)as name
from @TABLE_OLD a full outer join @TABLE_NEW b on a.id='s'+b.id
order by isnull(a.id,'S'+b.id)/*
id name
---------- ----------
S001 张
S002 李
S003 赵
S004 old孙
*/借水族的测试环境用一下
--
--
--出来的结构应该是:
--table_2009
-----------
--id name
--S001 张
--S002 李
--S003 赵
--004 old孙declare @table_old table(id varchar(10),name varchar(10))
insert into @table_old
select 'S001', '张' union all
select 'S002', '王' union all
select 'S004', '孙' declare @table_new table(id varchar(10),name varchar(10))
insert into @table_new
select '001', '张' union all
select '002', '李' union all
select '003', '赵' ;with cte as
(
select id,name,type = 'old' from @table_old
union all
select id,name,type = 'new' from @table_new
)
,cte1 as
(
select id = stuff(id,1,charindex('S',id),''),
name,
type
from cte a
)
select distinct 'S'+id as id,name into table_2009 from
(
select id,
name = case when exists(select 1 from cte1 where id = a.id and name <> a.name and type <> a.type and a.type = 'old')
then (select distinct name from cte1 where a.id = id and type = 'new') else a.name end
from cte1 a
)Tselect * from table_2009drop table table_2009
/*
S001 张
S002 李
S003 赵
S004 孙
*/
--
--
--出来的结构应该是:
--table_2009
-----------
--id name
--S001 张
--S002 李
--S003 赵
--004 old孙declare @table_old table(id varchar(10),name varchar(10))
insert into @table_old
select 'S001', '张' union all
select 'S002', '王' union all
select 'S004', '孙' declare @table_new table(id varchar(10),name varchar(10))
insert into @table_new
select '001', '张' union all
select '002', '李' union all
select '003', '赵' ;with cte as
(
select id,name,type = 'old' from @table_old
union all
select id,name,type = 'new' from @table_new
)
,cte1 as
(
select id = stuff(id,1,charindex('S',id),''),
name,
type
from cte a
)
select distinct 'S'+id as id,name into table_2009 from
(
select id,
name = case when exists(select 1 from cte1 where id = a.id and type = 'new')
then (select distinct name from cte1 where a.id = id and type = 'new') else 'old'+a.name end
from cte1 a
)Tselect * from table_2009drop table table_2009
/*
S001 张
S002 李
S003 赵
S004 old孙
*/