现在有两个表,
表1:
ID A B C D
1 x x x
2 x x x
3 x x x
表2:
ID E F
1 a b
1 c d
2 e f
2 g h
3 i j
3 k l
3 m n现在想利用两张表中的ID将表2中的ID和表1ID相同的部分结合起来放到表1的D中,结果如下:
表1(insert后):
ID A B C D
1 x x x a_b/c_d
2 x x x e_f/g_h
3 x x x i_j/k_l/m_n 请问sql语句应如何实现?
表1:
ID A B C D
1 x x x
2 x x x
3 x x x
表2:
ID E F
1 a b
1 c d
2 e f
2 g h
3 i j
3 k l
3 m n现在想利用两张表中的ID将表2中的ID和表1ID相同的部分结合起来放到表1的D中,结果如下:
表1(insert后):
ID A B C D
1 x x x a_b/c_d
2 x x x e_f/g_h
3 x x x i_j/k_l/m_n 请问sql语句应如何实现?
create table tb1(id int,a varchar,b varchar,c varchar,d varchar)
insert tb1
select 1,'x','x','x','' union all
select 2,'x','x','x','' union all
select 3,'x','x','x',''
gocreate table tb2(id int,e varchar,f varchar)
insert tb2
select 1,'a','b' union all
select 1,'c','d' union all
select 2,'e','f' union all
select 2,'g','h' union all
select 3,'i','j' union all
select 3,'k','l' union all
select 3,'m','n'
godeclare @s varchar(100)
declare @i int
declare @temp varchar(100)
select @i = (select count(*) from tb2 where id=1 )
set @s=''
while(@i>0)
begin
select top 1 @temp=e+'_'+f from tb2 where id =1
set @s=@temp+'/'+@s
set @i=@i-1
end
select substring(@s,0,len(@s))
-------------------------
a_b/a_b(所影响的行数为 1 行)
declare @表1 table (ID int,A varchar(1),B varchar(1),C varchar(1),D sql_variant)
insert into @表1
select 1,'x','x','x',null union all
select 2,'x','x','x',null union all
select 3,'x','x','x',nulldeclare @表2 table (ID int,E varchar(1),F varchar(1))
insert into @表2
select 1,'a','b' union all
select 1,'c','d' union all
select 2,'e','f' union all
select 2,'g','h' union all
select 3,'i','j' union all
select 3,'k','l' union all
select 3,'m','n'select ID,E+'_'+F as [value] into #t from @表2select aa.ID,aa.A,aa.B,aa.C,bb.[values] as D from @表1 aa left join (
select * from(select distinct id from #t)a outer apply(
select [values]= stuff(replace(replace(
(
select value from #t n
where id = a.id
for xml auto
), '<N value="', '/'), '"/>', ''), 1, 1, '')
)N ) bb on aa.id=bb.id
drop table #t
/*
ID A B C D
----------- ---- ---- ---- ----------------
1 x x x a_b/c_d
2 x x x e_f/g_h
3 x x x i_j/k_l/m_n
*/
先把表B中字段E和字段F的数据合并放入临时表,然后通过字符串合并~(函数也行,这个方法也行)和表A连接查询,得到最后的结果。建立函数CREATE FUNCTION dbo.getstr(@ID int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000)
SET @s = ''
SELECT @s = @s + '/' + value FROM #t WHERE ID=@ID
RETURN STUFF(@s, 1, 1, '')
END
GO
-----------------------------------------------------
调用
select ID,dbo.getstr(ID) as value from #t ID value
1 a_b/c_d
2 e_f/g_h
3 i_j/k_l/m_n
RETURNS varchar(8000)
AS
BEGIN
DECLARE @s Nvarchar(8000)
SET @s = ''
SELECT @s = ISNULL(@s + '/','') + LTRIM(E+F) FROM (SELECT ID,E+F FROM TB)AS T WHERE ID=@ID
END
GO2000
/***********************************************--> 测试数据:[表1]
--> 测试时间:2009-08-13 09:23:49
--> 我的淘宝:<<戒色坊>> http://shop36766744.taobao.com/***********************************************/if object_id('[表1]') is not null drop table [表1]
create table [表1]([ID] int,[A] varchar(1),[B] varchar(1),[C] varchar(1),[D] varchar(20))
insert [表1]
select 1,'x','x','x',null union all
select 2,'x','x','x',null union all
select 3,'x','x','x',nullif object_id('[表2]') is not null drop table [表2]
create table [表2]([ID] int,[E] varchar(1),[F] varchar(1))
insert [表2]
select 1,'a','b' union all
select 1,'c','d' union all
select 2,'e','f' union all
select 2,'g','h' union all
select 3,'i','j' union all
select 3,'k','l' union all
select 3,'m','n'update [表1] set [D]= [value] from (
select id, [value] = stuff((select '/' + E+'_'+F from [表2] where id = t.id for xml path('')) , 1 , 1 , '')
from [表2] t
group by id) a join [表1] b on a.id=b.idselect * from [表1]
/*
ID A B C D
----------- ---- ---- ---- --------------------
1 x x x a_b/c_d
2 x x x e_f/g_h
3 x x x i_j/k_l/m_n(3 行受影响)*/
drop table [表1],[表2]
insert into #tmp1
select 1,'x','x','x',null union all
select 2,'x','x','x',null union all
select 3,'x','x','x',nullCREATE table #tmp2(ID int,E varchar(1),F varchar(1))
insert into #tmp2
select 1,'a','b' union all
select 1,'c','d' union all
select 2,'e','f' union all
select 2,'g','h' union all
select 3,'i','j' union all
select 3,'k','l' union all
select 3,'m','n'SELECT *, CAST(NULL AS VARCHAR(50)) AS union_char INTO #tmp3
FROM #tmp2DECLARE @id INT,
@tmp VARCHAR(50)UPDATE #tmp3
SET @tmp = CASE WHEN @id = ID THEN CASE WHEN @tmp IS NULL OR (@tmp = '') THEN '' ELSE @tmp + '/' END ELSE '' END + E + '_' + F,
union_char = @tmp,
@id = ID
FROM #tmp3UPDATE #tmp1
SET D = union_char
FROM #tmp1
INNER JOIN #tmp3 ON #tmp3.ID = #tmp1.IDSELECT *
FROM #tmp1DROP TABLE #tmp1
DROP TABLE #tmp2
DROP TABLE #tmp3/*
ID A B C D
----------- ---- ---- ---- ----------------
1 x x x a_b/c_d
2 x x x e_f/g_h
3 x x x i_j/k_l/m_n
*/
请问下,你用的哪个版本的啊? 2000里好像没有for xml