SQL请教，这里人气旺
IDX CODE DEPTCODE PARTNAME
--------------------
1 CN00001 CN00001001 PART1
2 CN00001 CN00001002 PART2
3 CN00001 CN00001001001 PART3
4 CN00001 CN00001002001 PART4
5 CN00002 CN00002001 PART5
6 CN00002 CN00002001001 PART6
IDX CODE DEPTCODE DETAIL
-----------------------------------------------
1 CN00001 CN0001001 CN0001001;PART1|CN0001001001;PART3
2 CN00001 CN0001002 CN0001002;PART2|CN0001002001;PART4
3 CN00002 CN0002001 CN0002001;PART5|CN0002001001;PART6
DEPTCODE是部门代码，规则是CODE+3位为第一级部门，以此类推，下级部门在上级部门代码上加3位
我想要的结果是只是列出第一级部门信息，下级部门信息归类成为DETAIL字段，请大侠指点；
上面是原数据，下面是希望得到的结果，
理论上有无穷多层级。
有一个帖子，有哥们回答：
insert ta select 1, 'CN00001 ', 'CN00001001 ' , 'PART1 '
union all select 2, 'CN00001 ', 'CN00001002 ', 'PART2 '
union all select 3, 'CN00001 ', 'CN00001001001 ', 'PART3 '
union all select 4, 'CN00001 ', 'CN00001002001 ', 'PART4 '
union all select 5, 'CN00002 ', 'CN00002001 ', 'PART5 '
union all select 6, 'CN00002 ', 'CN00002001001 ', 'PART6 '
改一下:
create function test_f(@DEPTCODE varchar(50))
returns varchar(100)
as
begin
declare @s varchar(100)
select @s=isnull(@s, ' ')+DEPTCODE+ ': '+PARTNAME+ '| ' from ta where charindex(@DEPTCODE,DEPTCODE)=1
return left(@s,len(@s)-1)
end
select IDX,CODE,DEPTCODE ,显示=dbo.test_f(DEPTCODE) from ta where len(dbo.test_f(DEPTCODE))> 21
1CN00001CN00001001CN00001001:PART1|CN00001001001:PART3
2CN00001CN00001002CN00001002:PART2|CN00001002001:PART4
5CN00002CN00002001CN00002001:PART5|CN00002001001:PART6
可以运行，但改成
create function test_f(@DEPTCODE varchar(50))
returns varchar(7000)
as
begin
declare @s varchar(7000)
select @s=isnull(@s, ' ')+DEPTCODE+ ': '+PARTNAME+ '| ' from ta where charindex(@DEPTCODE,DEPTCODE)=1 and status <> 0
return left(@s,len(@s)-1)
end
运行报错，说传入的参数长度错误，只是多了一点
and status <> 0 呀
status是表里面的一个整型字段，
大虾们有什么高招，请教
[解决办法]
create table ta(IDX int, CODE varchar(20), DEPTCODE varchar(50), PARTNAME varchar(20), status int)
insert ta select 1, 'CN00001 ', 'CN00001001 ', 'PART1 ', 2
union all select 2, 'CN00001 ', 'CN00001002 ', 'PART2 ', 0
union all select 3, 'CN00001 ', 'CN00001001001 ', 'PART3 ', 1
union all select 4, 'CN00001 ', 'CN00001002001 ', 'PART4 ', 2
union all select 5, 'CN00002 ', 'CN00002001 ', 'PART5 ', 2
union all select 6, 'CN00002 ', 'CN00002001001 ', 'PART6 ', 1
create function test_f(@DEPTCODE varchar(50))
returns varchar(7000)
as
begin
declare @s varchar(7000)
set @s= ' '
select @s=@s+DEPTCODE+ ': '+PARTNAME+ '| ' from ta
where charindex(@DEPTCODE, DEPTCODE)=1 and status <> 0
return left(@s,len(@s)-1)
end
go
select IDX,CODE,DEPTCODE,显示=dbo.test_f(DEPTCODE) from ta
where len(DEPTCODE)=10
--result
IDX CODE DEPTCODE 显示
----------- -------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------
1 CN00001 CN00001001 CN00001001:PART1|CN00001001001:PART3
2 CN00001 CN00001002 CN00001002001:PART4
5 CN00002 CN00002001 CN00002001:PART5|CN00002001001:PART6
(3 row(s) affected)
[解决办法]
up
[解决办法]
create function f_get_detail (@code varchar(80), @deptcode varchar(80))
returns varchar(8000)
as
begin
declare @s_detail varchar(8000)
declare @lenth int
set @s_detail = ' '
select @lenth = len(rtrim(ltrim(@code)))
-- while @@rowcount <> 0
-- begin
--set @lenth = @lenth + 3
select @s_detail = @s_detail + '| ' + deptcode + ': ' + partname
from t_code
where code = @code
and substring(deptcode , 1 , 10 ) = @deptcode
--and len(rtrim(ltrim(deptcode))) = @lenth
-- end
select @s_detail = stuff(@s_detail , 1 ,1 , ' ')
return @s_detail
end
create table t_code
(
idx int,
CODE varchar(80) ,
DEPTCODE varchar(80) ,
PARTNAME varchar(80)
)
insert into t_code
select 1, 'CN00001 ', 'CN00001001 ' , 'PART1 ' union
select 2, 'CN00001 ', 'CN00001002 ' , 'PART2 ' union
select 3, 'CN00001 ', 'CN00001001001 ' , 'PART3 ' union
select 4, 'CN00001 ', 'CN00001002001 ' , 'PART4 ' union
select 5, 'CN00002 ', 'CN00002001 ' , 'PART5 ' union
select 6, 'CN00002 ', 'CN00002001001 ' , 'PART6 '
select idx ,
CODE,
deptcode,
dbo.f_get_detail(code , deptcode)
from t_code
where len(rtrim(ltrim(code))) = len(rtrim(ltrim(deptcode))) - 3
