--2005 --> 测试数据:[表1] if object_id('[表1]') is not null drop table [表1] go create table [表1]([ID] varchar(3),[NAME] varchar(2),[F_NAME] varchar(2),[CODE] varchar(30),[F_ID] varchar(30)) insert [表1] select '001','A1','SE',null,null union all select '002','B1','54',null,null union all select '003','A1','SE',null,null --> 测试数据:[表2] if object_id('[表2]') is not null drop table [表2] go create table [表2]([ID] varchar(4),[NAME] varchar(2),[F_NAME] varchar(2),[CODE] int) insert [表2] select 'x124','A1','SE',47 union all select 'x125','C1','SX',78 union all select 'x126','A1','SE',13 union all select 'x127','A1','SE',47 --> 测试数据:[表3] if object_id('[表3]') is not null drop table [表3] go create table [表3]([ID] varchar(4),[NAME] varchar(2),[F_NAME] varchar(2)) insert [表3] select 'n001','A1','SE'delete t from [表1] t where [ID] in (select max([ID]) from [表1] group by [NAME],[F_NAME] having count(1) >= 2)update t set code = stuff((select ','+ltrim([CODE]) from (select distinct [CODE] from [表2] where [NAME]=t.[NAME] and [F_NAME] = t.[F_NAME]) r For XML PATH('')),1,1,'') from [表1] t update t set [F_ID] = stuff((select ','+[ID] from (select [ID],[NAME],[F_NAME] from [表2] union select [ID],[NAME],[F_NAME] from [表3]) r where [NAME]=t.[NAME] and [F_NAME] = t.[F_NAME] For XML PATH('')),1,1,'') from [表1] tselect * from [表1] ID NAME F_NAME CODE F_ID ---- ---- ------ ------------------------------ ------------------------------ 001 A1 SE 13,47 n001,x124,x126,x127 002 B1 54 NULL NULL(2 行受影响)
试一下睡觉use pubs --建表 if object_id('t1') is not null drop table t1 go create table t1 ( [id] varchar(10) ,[name] varchar(20) ,[F_NAME] varchar(20) ,[CODE] varchar(100) ,[F_ID] varchar(100) ) go if object_id('t2') is not null drop table t2 go create table t2 ( [id] varchar(10) ,[name] varchar(20) ,[F_NAME] varchar(20) ,[CODE] varchar(100) ) go if object_id('t3') is not null drop table t3 go create table t3 ( [id] varchar(10) ,[name] varchar(20) ,[F_NAME] varchar(20) ) go--插入数据 insert into t1 select '001','A1','SE','','' union all select '002','B1','54','','' union all select '003','A1','SE','','' insert into t2 select 'x124','A1','SE','47' union all select 'x125','C1','SX','78' union all select 'x126','A1','SE','13' union all select 'x127','A1','SE','47' insert into t3 select 'n001','A1','SE' --1、表1因为NAME,F_NAME 有重复,删除 一条 delete from t1 where t1.[id] in (select a.id from t1 a,t1 b where b.[id]<a.[id] and b.[name]=a.[name] and b.[F_NAME]=a.[F_NAME] group by a.id )/*--删除后查询结果 001 A1 SE 002 B1 54 */--2.当2个表的NAME,F_NAME相同,把表2中可能出现的CODE写进表1 CODE (不能重复,如47出现2次,但只写一次) --创建函数 if object_id('Get_Str') is not null drop function Get_Str go create function Get_Str ( @name varchar(20) ,@F_NAME varchar(20) ,@pars int ) returns varchar(800) as begin declare @chr varchar(800) set @chr='' if @pars=1 begin select @chr=@chr+t2.code+',' from (select distinct [name],F_NAME,code from t2)t2 where t2.[name]=@name and t2.[F_NAME]=@F_NAME end if @pars=2 begin select @chr=@chr+t2.[id]+',' from t2 where t2.[name]=@name and t2.[F_NAME]=@F_NAME select @chr=@chr+t3.[id]+',' from t3 where t3.[name]=@name and t3.[F_NAME]=@F_NAME end return left(@chr,case when len(@chr)-1>0 then len(@chr)-1 else len(@chr) end) end go--语句 sql2000 select [id],[name],[F_NAME],dbo.Get_Str([name],[F_NAME],1) as CODE ,dbo.Get_Str([name],[F_NAME],2) as F_ID from t1--查询结果 /* ID(主键) NAME F_NAME CODE F_ID 001 A1 SE 13,47 x124,x126,x127,n001 002 B1 54 */
Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE FUNCTION GET_STRING(@ID INT) RETURNS VARCHAR(50) AS BEGIN DECLARE @CLASS VARCHAR(50) SELECT @CLASS=ISNULL(@CLASS+'/','')+LTRIM(B) FROM T WHERE A=@ID RETURN @CLASS END
--> 测试数据:[表1]
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([ID] varchar(3),[NAME] varchar(2),[F_NAME] varchar(2),[CODE] varchar(30),[F_ID] varchar(30))
insert [表1]
select '001','A1','SE',null,null union all
select '002','B1','54',null,null union all
select '003','A1','SE',null,null
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([ID] varchar(4),[NAME] varchar(2),[F_NAME] varchar(2),[CODE] int)
insert [表2]
select 'x124','A1','SE',47 union all
select 'x125','C1','SX',78 union all
select 'x126','A1','SE',13 union all
select 'x127','A1','SE',47
--> 测试数据:[表3]
if object_id('[表3]') is not null drop table [表3]
go
create table [表3]([ID] varchar(4),[NAME] varchar(2),[F_NAME] varchar(2))
insert [表3]
select 'n001','A1','SE'delete t
from [表1] t
where [ID] in (select max([ID]) from [表1] group by [NAME],[F_NAME] having count(1) >= 2)update t
set code = stuff((select ','+ltrim([CODE])
from (select distinct [CODE] from [表2]
where [NAME]=t.[NAME] and [F_NAME] = t.[F_NAME]) r For XML PATH('')),1,1,'')
from [表1] t
update t
set [F_ID] = stuff((select ','+[ID]
from (select [ID],[NAME],[F_NAME] from [表2]
union
select [ID],[NAME],[F_NAME] from [表3]) r where [NAME]=t.[NAME] and [F_NAME] = t.[F_NAME]
For XML PATH('')),1,1,'')
from [表1] tselect * from [表1]
ID NAME F_NAME CODE F_ID
---- ---- ------ ------------------------------ ------------------------------
001 A1 SE 13,47 n001,x124,x126,x127
002 B1 54 NULL NULL(2 行受影响)
--建表
if object_id('t1') is not null drop table t1
go
create table t1
(
[id] varchar(10)
,[name] varchar(20)
,[F_NAME] varchar(20)
,[CODE] varchar(100)
,[F_ID] varchar(100)
)
go
if object_id('t2') is not null drop table t2
go
create table t2
(
[id] varchar(10)
,[name] varchar(20)
,[F_NAME] varchar(20)
,[CODE] varchar(100)
)
go
if object_id('t3') is not null drop table t3
go
create table t3
(
[id] varchar(10)
,[name] varchar(20)
,[F_NAME] varchar(20)
)
go--插入数据
insert into t1
select '001','A1','SE','','' union all
select '002','B1','54','','' union all
select '003','A1','SE','','' insert into t2
select 'x124','A1','SE','47' union all
select 'x125','C1','SX','78' union all
select 'x126','A1','SE','13' union all
select 'x127','A1','SE','47' insert into t3
select 'n001','A1','SE' --1、表1因为NAME,F_NAME 有重复,删除 一条
delete from t1 where t1.[id] in
(select a.id from t1 a,t1 b
where b.[id]<a.[id] and b.[name]=a.[name] and b.[F_NAME]=a.[F_NAME] group by a.id )/*--删除后查询结果
001 A1 SE
002 B1 54
*/--2.当2个表的NAME,F_NAME相同,把表2中可能出现的CODE写进表1 CODE (不能重复,如47出现2次,但只写一次)
--创建函数
if object_id('Get_Str') is not null drop function Get_Str
go
create function Get_Str
(
@name varchar(20)
,@F_NAME varchar(20)
,@pars int
)
returns varchar(800)
as
begin
declare @chr varchar(800)
set @chr=''
if @pars=1
begin
select @chr=@chr+t2.code+',' from (select distinct [name],F_NAME,code from t2)t2 where t2.[name]=@name and t2.[F_NAME]=@F_NAME end
if @pars=2
begin
select @chr=@chr+t2.[id]+',' from t2 where t2.[name]=@name and t2.[F_NAME]=@F_NAME
select @chr=@chr+t3.[id]+',' from t3 where t3.[name]=@name and t3.[F_NAME]=@F_NAME
end
return left(@chr,case when len(@chr)-1>0 then len(@chr)-1 else len(@chr) end)
end
go--语句 sql2000
select [id],[name],[F_NAME],dbo.Get_Str([name],[F_NAME],1) as CODE
,dbo.Get_Str([name],[F_NAME],2) as F_ID
from t1--查询结果
/*
ID(主键) NAME F_NAME CODE F_ID
001 A1 SE 13,47 x124,x126,x127,n001
002 B1 54
*/
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION GET_STRING(@ID INT)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @CLASS VARCHAR(50)
SELECT @CLASS=ISNULL(@CLASS+'/','')+LTRIM(B) FROM T WHERE A=@ID
RETURN @CLASS
END