A表
ID CODE PRE_CODE PREID
1 1001
2 1002 1
3 1003 1
4 1004 3
5 1006
6 1007 6
7 1008 3
7 1008 4
根据PREID字段更新PRE_CODE,结果如下:
A表更新后
ID CODE PRE_CODE PREID
1 1001
2 1002 1001 1
3 1003 1001 1
4 1004 1003 3
5 1006
6 1007 1006 6
7 1008 1003,1004 3
7 1008 1003,1004 4
基本思路:如果PREID不为空,从表中查询ID=PREID的CODE更新到PRE_CODE字段,如果有多条则用逗号','分隔
ID CODE PRE_CODE PREID
1 1001
2 1002 1
3 1003 1
4 1004 3
5 1006
6 1007 6
7 1008 3
7 1008 4
根据PREID字段更新PRE_CODE,结果如下:
A表更新后
ID CODE PRE_CODE PREID
1 1001
2 1002 1001 1
3 1003 1001 1
4 1004 1003 3
5 1006
6 1007 1006 6
7 1008 1003,1004 3
7 1008 1003,1004 4
基本思路:如果PREID不为空,从表中查询ID=PREID的CODE更新到PRE_CODE字段,如果有多条则用逗号','分隔
insert into tb
select 1, '1001','',''
union all select 2, '1002','','1'
union all select 3, '1003', '','1'
union all select 4, '1004', '','3'
union all select 5, '1006', '',''
union all select 6, '1007', '','6'
union all select 7, '1008', '','3'
union all select 7, '1008', '','4' select id,code,pre_code=isnull((select top 1 code from tb where a.preid=id),''),preid from tb a/*
id code pre_code preid
--------------------------------------------
1 1001
2 1002 1001 1
3 1003 1001 1
4 1004 1003 3
5 1006
6 1007 1007 6
7 1008 1003 3
7 1008 1004 4*/
drop table tb
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-12-04 11:34:01
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID int,CODE int,PREID int,PRE_CODE varchar(100))
INSERT INTO @tb
SELECT 1,1001,null,null UNION ALL
SELECT 2,1002,1,null UNION ALL
SELECT 3,1003,1,null UNION ALL
SELECT 4,1004,3,null UNION ALL
SELECT 5,1006,null,null UNION ALL
SELECT 6,1007,6,null UNION ALL
SELECT 7,1008,3,null UNION ALL
SELECT 7,1008,4,null--SQL查询如下:UPDATE A SET
PRE_CODE = STUFF(B.x,1,1,'')
FROM @tb AS A
CROSS APPLY(
SELECT x = (
SELECT ','+RTRIM(C.CODE)
FROM @tb AS B
JOIN @tb AS C
ON B.PREID = C.ID
WHERE B.CODE = A.CODE
ORDER BY C.ID
FOR XML PATH('')
)
) AS B;
SELECT * FROM @tb;
*************************************
* T-MAC 小编 *
* -->努力成长中 *
* -->梦想DBA *
*************************************
*/
if OBJECT_ID('tb') is not null
drop table tb
go
create table tb (ID int, CODE int, PRE_CODE int, PREID int)
insert tb(id,code,preid) select
1, 1001 ,null union select
2, 1002, 1 union select
3, 1003, 1 union select
4, 1004, 3 union select
5, 1006, null union select
6, 1007 ,5 union select
7, 1008 ,3 union select
7, 1008, 4
go
with cte as
(
select ID,code,preid,path=CAST('' as varchar(30)),lev=1from tb where PREID is null
union all
select k.id,k.code,k.preid,path=cast(c.path+case when lev=1 then '' else ',' end +rtrim(c.CODE) as varchar(30)),lev+1
from cte c join tb k on c.ID=k.PREID
)
select c.ID,tb.CODE,
PRE_CODE=path,
PREID=tb.PREID
from cte c join tb on c.ID=tb.ID
/*(8 行受影响)
ID CODE PRE_CODE PREID
----------- ----------- ------------------------------ -----------
1 1001 NULL
2 1002 1001 1
3 1003 1001 1
4 1004 1001,1003 3
5 1006 NULL
6 1007 1006 5
7 1008 1001,1003 3
7 1008 1001,1003,1004 3
7 1008 1001,1003 4
7 1008 1001,1003,1004 4(10 行受影响)
*/
(
select ID,code,preid,path=CAST('' as varchar(30)),lev=1from tb where PREID is null
union all
select k.id,k.code,k.preid,path=cast(c.path+case when lev=1 then '' else ',' end +rtrim(c.CODE) as varchar(30)),lev+1
from cte c join tb k on c.ID=k.PREID
)select c.ID,tb.CODE,
PRE_CODE=path,
PREID=tb.PREID
from (select max(path) as path , id from cte group by id )c join tb on c.ID=tb.ID
/*ID CODE PRE_CODE PREID
----------- ----------- ------------------------------ -----------
1 1001 NULL
2 1002 1001 1
3 1003 1001 1
4 1004 1001,1003 3
5 1006 NULL
6 1007 1006 5
7 1008 1001,1003,1004 3
7 1008 1001,1003,1004 4
*/
好像理解错误
if object_id('tb') is not null drop table tb
create table tb([ID] int,[CODE] varchar(20),[PRE_CODE] varchar(20),[PREID] int)
insert tb
select 1,'1001',null,null union all
select 2,'1002',null,1 union all
select 3,'1003',null,1 union all
select 4,'1004',null,3 union all
select 5,'1006',null,null union all
select 6,'1007',null,6 union all
select 7,'1008',null,3 union all
select 7,'1008',null,4
--创建函数
create function dbo.getcode(@code varchar(20))
returns varchar(50)
as
begin
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + ','+code from tb where id in
(select PREID from tb where code = @code)
return stuff(@sql,1,1,'')
end--更新
update tb
set PRE_CODE = dbo.getcode(code)select * from tb
--结果
-----------------------
1 1001 NULL NULL
2 1002 1001 1
3 1003 1001 1
4 1004 1003 3
5 1006 NULL NULL
6 1007 1007 6
7 1008 1003,1004 3
7 1008 1003,1004 4
declare @sql varchar(8000)
select @sql = ''
select @sql = @sql + ','+code from tb where id in
(select PREID from tb where code = @code)
return stuff(@sql,1,1,'')
end
应该可以的吧