两个表链接问题

[pollux0823]

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孙

[pollux0823]

table_2009 里的第四条 数据应该是 S004 old孙， ID前少加了个S

另外 我怎么没有修改帖子权限呢

[jinjazz]

select 
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

[jinjazz]

[Quote=引用 1 楼 pollux0823 的回复:]
table_2009 里的第四条 数据应该是 S004 old孙， ID前少加了个S

另外 我怎么没有修改帖子权限呢
[/Quote]

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

[Zoezs]

做个试图

create view vTB
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

[jinjazz]

上面的不对，应该是

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

[水族杰纶]

DECLARE @table_old TABLE(id VARCHAR(10),  name NVARCHAR(10)) 
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孙*/

[jinjazz]

DECLARE @table_old TABLE(id VARCHAR(10),  name NVARCHAR(10)) 
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孙
*/

借水族的测试环境用一下

[lg3605119]

--
--
--出来的结构应该是： 
--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
)T

select * from table_2009

drop table table_2009
/*
S001	张
S002	李
S003	赵
S004	孙
*/

[lg3605119]

--
--
--出来的结构应该是： 
--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
)T

select * from table_2009

drop table table_2009
/*
S001	张
S002	李
S003	赵
S004	old孙
*/