关于字段关联

Question

我有2个表
一个USER表
ID  NAME 
1    aa   
2    bb   
3    cc   一个TRACE表
ID    TRACE_NAME      WAIT_USER   
1        T_1            1;2;3;    
2        T_2            1;2
3        T_3            2;3
4        T_4            1;2我需要写个查询 结果:
TRACE_USER
ID     TRACE_NAME    WAIT_USER_NAME 
1        T_1            aa;bb;cc   
2        T_2            aa;bb
3        T_3            bb;cc
4        T_4            aa;bb哪位高手能帮帮我 谢谢

Answer 1

表这样设计不符合范式，很难控制，后面做复杂操作几乎不可能，最好改为
ID    TRACE_NAME      WAIT_USER  
1        T_1            1 
5        T_1            2 
6        T_1            3 
……

Answer 2

--> 测试数据：[USER]
if object_id('[USER]') is not null drop table [USER]
create table [USER]([ID] int,[NAME] varchar(2))
insert [USER]
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc'
--> 测试数据：[TRACE]
if object_id('[TRACE]') is not null drop table [TRACE]
create table [TRACE]([ID] int,[TRACE_NAME] varchar(3),[WAIT_USER] varchar(30))
insert [TRACE]
select 1,'T_1','1;2;3' union all
select 2,'T_2','1;2' union all
select 3,'T_3','2;3' union all
select 4,'T_4','1;2'select * into TRACE_USER  from [TRACE]while exists(select 1 from TRACE_USER t,[USER] r
where charindex(';'+ltrim(r.ID)+';',';'+t.WAIT_USER+';')>0)
begin
update t 
set WAIT_USER = replace(WAIT_USER,r.[ID],r.[NAME])
from TRACE_USER t,[USER] r
where charindex(';'+ltrim(r.ID)+';',';'+t.WAIT_USER+';')>0
endselect * from TRACE_USER drop table TRACE_USER ----------------------------
1 T_1 aa;bb;cc
2 T_2 aa;bb
3 T_3 bb;cc
4 T_4 aa;bb

Answer 3

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-01-15 12:04:57
-- Version:
--      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
-- Aug  6 2000 00:57:48 
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#USER
if object_id('tempdb.dbo.#USER') is not null drop table #USER
go 
create table #USER([ID] int,[NAME] varchar(2))
insert #USER
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc'
--> 测试数据：#TRACE
if object_id('tempdb.dbo.#TRACE') is not null drop table #TRACE
go 
create table #TRACE([ID] int,[TRACE_NAME] varchar(3),[WAIT_USER] varchar(5))
insert #TRACE
select 1,'T_1','1;2;3' union all
select 2,'T_2','1;2' union all
select 3,'T_3','2;3' union all
select 4,'T_4','1;2'
--------------开始查询--------------------------
SELECT * INTO TTB FROM #TRACE WHERE 1<>1INSERT TTB SELECT 
T.ID,T.TRACE_NAME,U.NAME 
FROM 
#TRACE T,#USER U  
WHERE CHARINDEX(';'+LTRIM(U.ID)+';',';'+WAIT_USER+';')>0
ALTER  FUNCTION GET_STRING(@ID INT)
RETURNS VARCHAR(50)
AS 
  BEGIN 
       DECLARE @CLASS VARCHAR(50)
       SELECT @CLASS=ISNULL(@CLASS+',','')+LTRIM(WAIT_USER) FROM TTB WHERE ID=@ID
       RETURN @CLASS
  ENDSELECT ID,TRACE_NAME,DBO.GET_STRING(ID)AS NAME FROM TTB GROUP BY ID,TRACE_NAME
----------------结果----------------------------
/* 
ID          TRACE_NAME NAME                                               
----------- ---------- -------------------------------------------------- 
1           T_1        aa,bb,cc
2           T_2        aa,bb
3           T_3        bb,cc
4           T_4        aa,bb（所影响的行数为 4 行）
*/

Answer 4

分解字符串包含的信息值后然后合并到另外一表的信息
(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)  2007-12-23  广东深圳)/*问题描述
tba
ID  classid   name
1     1,2,3   西服 
2     2,3     中山装
3     1,3     名裤
tbb 
id   classname
1     衣服
2     上衣
3     裤子我得的结果是
id   classname            name
1     衣服,上衣,裤子      西服 
2     上衣，裤子          中山装
3     衣服，裤子          名裤
*/-----------------------------------------------------
--sql server 2000中的写法
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go--第1种方法，创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
  return stuff(@str,1,1,'')
end
go 
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id          classid       name       
----------- ------------- ---------- 
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
（所影响的行数为 3 行）
*/--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID          classid              name       
----------- -------------------- ---------- 
1           衣服,上衣,裤子       西服
2           上衣,裤子            中山装
3           衣服,裤子            名裤
（所影响的行数为 3 行）
*/
drop table tba,tbb------------------------------------------------------------------------
--sql server 2005中先分解tba中的classid,然后再合并classname
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3'  ,'中山装')
insert into tba values(3,'1,3'  ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
goSELECT id , classname , name FROM
(
  SELECT DISTINCT id , name FROM (select tbc.id , tbc.name , tbb.classname from 
  (
    SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
    OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
  ) tbc , tbb where tbc.classid = tbb.id
  ) T
)A 
OUTER APPLY
(
  SELECT [classname]= STUFF(REPLACE(REPLACE((
    SELECT classname FROM (select tbc.id , tbc.name , tbb.classname from 
    (
      SELECT A.id , A.name , B.classid FROM(SELECT id , name , [classid] = CONVERT(xml,'<root><v>' + REPLACE([classid], ',', '</v><v>') + '</v></root>') FROM tba)A
      OUTER APPLY(SELECT classid = N.v.value('.', 'varchar(100)') FROM A.[classid].nodes('/root/v') N(v))B
    ) tbc , tbb where tbc.classid = tbb.id
  ) N
  WHERE id = A.id and name = A.name
  FOR XML AUTO), '<N classname="', ','), '"/>', ''), 1, 1, '')
)N
order by iddrop table tba,tbb/*
id          classname      name
----------- -------------- ----------
1           衣服,上衣,裤子 西服
2           上衣,裤子      中山装
3           衣服,裤子      名裤
(3 行受影响)
*/

Answer 5

再凑一个 sql server 2005 的方法declare @user table(id int,name varchar(2));
insert into @user
 select 1,'aa' union all select 2,'bb' union all select 3,'cc';declare @trace table(id int,trace_name varchar(3),trace_user varchar(10));
insert into @trace
 select 1,'T_1','1;2;3;' union all select 2,'T_2','1;2' union all
 select 3,'T_3','2;3' union all select 4,'T_4','1;2';with cte as(
select t.id,t.trace_name,u.name
from @user u join @trace t on t.trace_user like '%'+ltrim(u.id)+'%')
select id,trace_name,
stuff((select ';'+[name] from cte where id=t.id for xml path('')),1,1,'')
from cte t group by id,trace_name
/*
1 T_1 aa;bb;cc
2 T_2 aa;bb
3 T_3 bb;cc
4 T_4 aa;bb
*/

Answer 6

SELECT * INTO TTB FROM #TRACE WHERE 1<>1INSERT TTB SELECT 
T.ID,T.TRACE_NAME,U.NAME 
FROM 
#TRACE T,#USER U  
WHERE CHARINDEX(';'+LTRIM(U.ID)+';',';'+WAIT_USER+';')>0这段我不用临时表SELECT * INTO TTB FROM TRACE WHERE 1<>1INSERT TTB SELECT 
TRACE.*,USER1.NAME 
FROM 
TRACE,USER1  
WHERE CHARINDEX(';'+LTRIM(USER1.ID)+';',';'+WAIT_USER+';')>0就出错了
需要怎么改?
出错信息
訊息 213，層級 16，狀態 1，行 3
插入錯誤: 資料行名稱或提供的數值數量與資料表定義不相符。

Answer 7

因为我测试数据生成的临时表,在函数里面不能用,所以在外面弄了一个表,INSERT TTB SELECT 有一个INSERT语句呀,当然会出错了,要不然得自己建立一个表,参考上面回复的吧

Answer 8

declare @user table(id int,name varchar(2));
insert into @user
 select 1,'aa' union all select 2,'bb' union all select 3,'cc';declare @trace table(id int,trace_name varchar(3),trace_user varchar(10));
insert into @trace
 select 1,'T_1','1;2;3;' union all select 2,'T_2','1;2' union all
 select 3,'T_3','2;3' union all select 4,'T_4','1;2';with cte as(
select t.id,t.trace_name,u.name
from @user u join @trace t on t.trace_user like '%'+ltrim(u.id)+'%')
select id,trace_name,
stuff((select ';'+[name] from cte where id=t.id for xml path('')),1,1,'')
from cte t group by id,trace_name
这个能不能写在一般的表中?
是需要怎么改语法

Answer 9

--第1种方法，创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
  declare @str varchar(1000)
  set @str=''
  select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
  return stuff(@str,1,1,'')
end
go 
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
这个方法里我需要把 间隔文字的  , 改成 ;
是需要改哪几个