typeCode typeName parentCode
KM4832 大类 0
KM0017 国家 KM4832
KM0018 一级 KM0017
KM0019 二级 KM0018--根据km0019,得到‘二级_一级_国家_大类’
KM4832 大类 0
KM0017 国家 KM4832
KM0018 一级 KM0017
KM0019 二级 KM0018--根据km0019,得到‘二级_一级_国家_大类’
解决方案 »
- SQL SERVER服务器按照默认方式配置成发布和分发服务器 问题sos~~~~~~~~~~~~~~~~~~~~~~~~~~~``
- 请教一个修改字段属性的Sql
- sql server 服务启动不了了
- 多表操作的问题
- !速结贴:设计数据表时有关逻辑型属性的设置问题。
- 循环插入问题!
- 为什么一个简单的插入语句这样的错误消息 102,级别 15,状态 1,第 2 行
- 在存储过程里使用游标速度会慢吗?有没有提高速度的好法子?
- 哪种方法连接服务器端 sql server7.0 速度快??
- 我想完成这样一个功能,是使用存储过程还是用触发器?
- sql语句中N'在变量时如何运用
- 一个SQL优化的问题 order by 对性能的影响
if OBJECT_ID('tb') is not null
drop table tb
go
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO
--2000的方法--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
declare @Level int
set @level=1
insert @t_level select @id,@level
while @@rowcount>0
begin
set @level=@level+1
insert @t_Level select tb.id,@level
from tb join @t_level t on tb.pid=t.id
where t.level+1=@level
end
return
endselect tb.*
from tb join dbo.f_cid('002') b
on tb.ID=b.id
/*
ID PID Name
---- ---- ----------
002 001 烟台市
004 002 招远市*/
go
--2005的方法(CTE)declare @n varchar(10)
set @n='002'
;with
jidian as
(
select * from tb where ID=@n
union all
select t.* from jidian j join tb t on j.ID=t.PID
)
select * from jidian
go
/*
ID PID Name
---- ---- ----------
002 001 烟台市
004 002 招远市
*/
go
--查找指定节点的所有父节点(标准树形,即一个子节点只有一个父节点)
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3))
AS
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
INSERT @t_Level SELECT @ID
SELECT @ID=PID FROM tb
WHERE ID=@ID
AND PID IS NOT NULL
END
RETURN
END
select tb.*
from tb join dbo.f_Pid('004') b
on tb.ID=b.id
/*
ID PID Name
---- ---- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
*/
go
--2005的方法
declare @n varchar(10)
set @n='004'
;with
fujidian as
(
select * from tb where ID=@n and PID is not null
union all
select a.* from tb a join fujidian f on a.ID=f.PID
)
select * from fujidian order by ID
/*
ID PID Name
---- ---- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
*/
INSERT INTO BOM SELECT 801,101
INSERT INTO BOM SELECT 801,102
INSERT INTO BOM SELECT 801,103
INSERT INTO BOM SELECT 801,601
INSERT INTO BOM SELECT 601,101
INSERT INTO BOM SELECT 601,105
INSERT INTO BOM SELECT 601,501
INSERT INTO BOM SELECT 501,106
INSERT INTO BOM SELECT 501,121
GOCREATE FUNCTION F_GETROOT(@PID INT)
RETURNS INT
AS
BEGIN
DECLARE @ID INT
WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)
BEGIN
SET @ID=@PID
SELECT @PID=PID FROM BOM WHERE ID=@ID
END
RETURN @PID
END
GOSELECT PID=DBO.F_GETROOT(PID),ID FROM BOM
GO/*
PID ID
----------- -----------
801 101
801 102
801 103
801 601
801 101
801 105
801 501
801 106
801 121
*/
DROP FUNCTION F_GETROOT
DROP TABLE BOM
GO
--生成测试数据
create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute varchar(20))
insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'
insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'
insert into BOM_1 select 3 ,'A2','A3',3,'生产'
insert into BOM_1 select 4 ,'A2','A4',2,'采购'
insert into BOM_1 select 5 ,'A3','A5',2,'采购'
insert into BOM_1 select 6 ,'A3','A6',1,'采购'
insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'
insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'
insert into BOM_1 select 9 ,'B2','B3',3,'生产'
insert into BOM_1 select 10,'B2','B4',2,'采购'
insert into BOM_1 select 11,'B3','B5',2,'采购'
insert into BOM_1 select 12,'B3','B6',2,'采购'
go
--创建用户定义函数,用于取每个父节点下子节点的采购配置信息
create function f_stock(@bom_head varchar(20))
returns @t table(bom varchar(20),number int)
as
begin
declare @level int
declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int)
set @level=1 if exists(select 1 from BOM_1 where bom_head=@bom_head)
insert into @a
select bom_child,number,products_attribute,@level
from BOM_1
where bom_head=@bom_head
while exists(select 1 from @a where [level]=@level and products_attribute='生产')
begin
set @level=@level+1
insert into @a(bom,number,products_attribute,[level])
select a.bom_child,a.number,a.products_attribute,@level
from BOM_1 a,@a b
where a.bom_head=b.bom and b.[level]=@level-1
end
insert into @t(bom,number) select bom,number from @a where products_attribute='采购'
return
end
go
--执行调用,取父节点'A'一个标准配置分解的采购信息及数量
select * from dbo.f_stock('A')
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
ID
----
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM创建用户定义函数,每个子节点de父节点的信息
--生成测试数据
create table BOM(ID int,parentID int,sClassName varchar(10))
insert into BOM values(1,0,'1111' )
insert into BOM values(2,1,'1111_1' )
insert into BOM values(3,2,'1111-1-1' )
insert into BOM values(4,3,'1111-1-1-1')
insert into BOM values(5,1,'1111-2' )go--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID int)
returns varchar(40)
as
begin
declare @ret varchar(40) while exists(select 1 from BOM where ID=@ID and parentID<>0)
begin
select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')
from
BOM a,BOM b
where
a.ID=@ID and b.ID=a.parentID
end
set @ret=stuff(@ret,1,1,'')
return @ret
end
go--执行查询
select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM
go--输出结果
/*
ID parentID
----------- ----------------------------------------
1
2 1
3 1,2
4 1,2,3
5 1
*/--删除测试数据
drop function f_getParent
drop table BOM
go
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([typeCode] varchar(6),[typeName] varchar(4),[parentCode] varchar(6))
insert [TB]
select 'KM4832','大类','0' union all
select 'KM0017','国家','KM4832' union all
select 'KM0018','一级','KM0017' union all
select 'KM0019','二级','KM0018'declare @s varchar(10)
set @s ='KM0019';with
l8r as (
select * from tb where [typeCode]=@s union all
select B.* from l8r a join tb b on a.[parentCode]=b.[typeCode]
)
select * from l8r
/*
typeCode typeName parentCode
-------- -------- ----------
KM0019 二级 KM0018
KM0018 一级 KM0017
KM0017 国家 KM4832
KM4832 大类 0(4 行受影响)*/drop table TB
create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go --查询指定节点及其所有子节点的函数
create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)
as
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id , @level
from tb a , @t_Level b
where a.pid = b.id and b.level = @level - 1
end
return
end
go --调用函数查询001(广东省)及其所有子节点
select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市
003 001 深圳市
004 002 天河区
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇 (所影响的行数为 10 行)
*/ --调用函数查询002(广州市)及其所有子节点
select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
002 001 广州市
004 002 天河区 (所影响的行数为 2 行)
*/ --调用函数查询003(深圳市)及其所有子节点
select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
003 001 深圳市
005 003 罗湖区
006 003 福田区
007 003 宝安区
008 007 西乡镇
009 007 龙华镇
010 007 松岗镇 (所影响的行数为 7 行)
*/ drop table tb
drop function f_cid
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-28 10:28:27
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (typeCode VARCHAR(6),typeName VARCHAR(4),parentCode VARCHAR(6))
INSERT INTO @T
SELECT 'KM4832','大类','0' UNION ALL
SELECT 'KM0017','国家','KM4832' UNION ALL
SELECT 'KM0018','一级','KM0017' UNION ALL
SELECT 'KM0019','二级','KM0018'--SQL查询如下:DECLARE @str nvarchar(MAX);DECLARE @typeCode varchar(10);
SET @typeCode = 'KM0019';SELECT @str = typeName,@typeCode = parentCode FROM @T WHERE typeCode = @typeCode;
WHILE @@ROWCOUNT > 0
BEGIN
SELECT @str = @str + ',' + typeName,@typeCode = parentCode
FROM @T WHERE typeCode = @typeCode;
ENDSELECT @str AS path;/*
path
-----------------------------------
二级,一级,国家,大类(1 行受影响)
*/
表结构如下:
ptype subptype amount
a a.1 20
a a.2 15
a a.3 10
a.1 a.1.1 20
a.1 a.1.2 15
a.1 a.1.3 30
a.2 a.2.1 10
a.2 a.2.2 20
a.1.1 a.1.1.1 45
a.1.1 a.1.1.2 15
a.2.1 a.2.1.1 20
a.2.2 a.2.2.1 13函数如下:
CREATE FUNCTION fn_aaa (@ProductID varchar(5))
RETURNS @retPLExpand TABLE (Ptype varchar(50),SubPtype varchar(50),
Amount smallint NOT NULL
)
-- title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
DECLARE @RowsAdded int
declare @PLExpand Table (Ptype varchar(50),SubPtype varchar(50),
Amount smallint NOT NULL,
processed tinyint default 0) INSERT @PLExpand
SELECT Ptype,SubPtype, Amount, 0
FROM aaa
WHERE Ptype = @ProductID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration WHILE @RowsAdded > 0
BEGIN
/*Mark all employee records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @PLExpand
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees ed 1.
INSERT @PLExpand
SELECT Ptype, SubPtype, Amount, 0
FROM aaa
WHERE ltrim(Ptype) in (select ltrim(subptype) from @PLExpand where processed = 1)
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @PLExpand
SET processed = 2
WHERE processed = 1
END
-- copy to the result of the function the required columns
INSERT @retPLExpand
SELECT Ptype, SubPtype, Amount
FROM @PLExpand
RETURN
END调用方法如下:
select * from fn_aaa('a.1')
意思是找出a.1下的所有儿子及孙子.
/*
标题:查询指定节点及其所有父节点的函数
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-05-12
地点:广东深圳
*/create table tb(id varchar(3) , pid varchar(3) , name varchar(10))
insert into tb values('001' , null , '广东省')
insert into tb values('002' , '001' , '广州市')
insert into tb values('003' , '001' , '深圳市')
insert into tb values('004' , '002' , '天河区')
insert into tb values('005' , '003' , '罗湖区')
insert into tb values('006' , '003' , '福田区')
insert into tb values('007' , '003' , '宝安区')
insert into tb values('008' , '007' , '西乡镇')
insert into tb values('009' , '007' , '龙华镇')
insert into tb values('010' , '007' , '松岗镇')
go--查询指定节点及其所有父节点的函数
create function f_pid(@id varchar(3)) returns @t_level table(id varchar(3))
as
begin
insert into @t_level select @id
select @id = pid from tb where id = @id and pid is not null
while @@ROWCOUNT > 0
begin
insert into @t_level select @id
select @id = pid from tb where id = @id and pid is not null
end
return
end
go--调用函数查询002(广州市)及其所有父节点
select a.* from tb a , f_pid('002') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
002 001 广州市(所影响的行数为 2 行)
*/--调用函数查询003(深圳市)及其所有父节点
select a.* from tb a , f_pid('003') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
003 001 深圳市(所影响的行数为 2 行)
*/--调用函数查询008(西乡镇)及其所有父节点
select a.* from tb a , f_pid('008') b where a.id = b.id order by a.id
/*
id pid name
---- ---- ----------
001 NULL 广东省
003 001 深圳市
007 003 宝安区
008 007 西乡镇(所影响的行数为 4 行)
*/drop table tb
drop function f_pid
returns varchar(500)
as
begin
declare @r varchar(500)
declare @parentCode varchar(20)
select @r = typeName,@parentCode = parentCode from tb where typeCode = @typeCode
if isnull(@parentCode,'0')<> '0'
set @r = @r + '_' + dbo.fn_GetName(@parentCode)
return @r
end
go--未测试
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( typeCod varchar(10), typeName varchar(10) , parentCode varchar(10))
go
insert tb SELECT
'KM4832' ,' 大类' , '0' UNION ALL SELECT
'KM0017' ,'国家' ,'KM4832' UNION ALL SELECT
'KM0018' , '一级' ,'KM0017' UNION ALL SELECT
'KM0019' , '二级' ,'KM0018'
go
declare @n varchar(10)
set @n='KM0019'
;with
fujidian as
(
select typeName=CAST(typeName as varchar(8000)),parentCode,lever=1
from tb
where typeCod=@n and parentCode is not null
union all
select cast(a.typeName as varchar(8000))+'_'+f.typeName ,a.parentCode ,lever+1
from tb a join fujidian f on a.typeCod=f.parentCode
)
select top 1 typeName from fujidian order by lever desc go
typeName
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
大类_国家_一级_二级
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( typeCod varchar(10), typeName varchar(10) , parentCode varchar(10))
go
insert tb SELECT
'KM4832' ,' 大类' , '0' UNION ALL SELECT
'KM0017' ,'国家' ,'KM4832' UNION ALL SELECT
'KM0018' , '一级' ,'KM0017' UNION ALL SELECT
'KM0019' , '二级' ,'KM0018'
go
declare @n varchar(10)
set @n='KM0019'
;with
fujidian as
(
select typeName=CAST(typeName as varchar(8000)),parentCode,lever=1
from tb
where typeCod=@n and parentCode is not null
union all
select f.typeName+'_'+cast(a.typeName as varchar(8000)) ,a.parentCode ,lever+1
from tb a join fujidian f on a.typeCod=f.parentCode
)
select top 1 typeName from fujidian order by lever desc go
typeName
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
二级_一级_国家_ 大类
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( typeCod varchar(10), typeName varchar(10) , parentCode varchar(10))
go
insert tb SELECT
'KM4832' ,' 大类' , '0' UNION ALL SELECT
'KM0017' ,'国家' ,'KM4832' UNION ALL SELECT
'KM0018' , '一级' ,'KM0017' UNION ALL SELECT
'KM0019' , '二级' ,'KM0018'
go
alter FUNCTION f_Pid(@ID varchar(13) )
RETURNS @t_Level TABLE(ID varchar(30),level int )
AS
BEGIN
declare @n int set @n=1
INSERT @t_Level SELECT @ID,@n
SELECT @ID=parentCode FROM tb
WHERE typeCod=@ID
AND parentCode IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
set @n=@n+1
INSERT @t_Level SELECT @ID ,@n
SELECT @ID=parentCode FROM tb
WHERE typeCod=@ID
AND parentCode IS NOT NULL
END
RETURN
END
declare @s varchar(1000)select @s=isnull(@s+'_','') +typeName
from tb join dbo.f_Pid('KM0019') b
on tb.typeCod=b.id order by [level]
select @s----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
二级_一级_国家_ 大类
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-28 10:32:46
if not object_id('tb') is null
drop table tb
Go
Create table tb([typeCode] nvarchar(6),[typeName] nvarchar(2),[parentCode] nvarchar(6))
Insert tb
select N'KM4832',N'大类',N'0' union all
select N'KM0017',N'国家',N'KM4832' union all
select N'KM0018',N'一级',N'KM0017' union all
select N'KM0019',N'二级',N'KM0018'
Go
declare @s nvarchar(100)
declare @P nvarchar(10)
set @p=N'KM0019'
while @@rowcount>0
select @p=[parentCode],@s=isnull(@s+'_','')+[typeName] from tb where [typeCode]=@p
select @s
/*
----------------------------------------------------------------------------------------------------
二级_一级_国家_大类(1 個資料列受到影響)*/
传入编号返回,二级_一级_国家_大类
f('km0019')正在测试以上几位的方法...
----------------
直接返回 '二级_一级_国家_大类 '
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( typeCod varchar(10), typeName varchar(10) , parentCode varchar(10))
go
insert tb SELECT
'KM4832' ,' 大类' , '0' UNION ALL SELECT
'KM0017' ,'国家' ,'KM4832' UNION ALL SELECT
'KM0018' , '一级' ,'KM0017' UNION ALL SELECT
'KM0019' , '二级' ,'KM0018'
go
create FUNCTION f_Pid(@ID varchar(13) )
RETURNS varchar(2000)
AS
BEGIN
declare @p table (ID varchar(30),level int )
declare @n int set @n=1
INSERT @p SELECT @ID,@n
SELECT @ID=parentCode FROM tb
WHERE typeCod=@ID
AND parentCode IS NOT NULL
WHILE @@ROWCOUNT>0
BEGIN
set @n=@n+1
INSERT @p SELECT @ID ,@n
SELECT @ID=parentCode FROM tb
WHERE typeCod=@ID
AND parentCode IS NOT NULL
END
declare @s varchar(1000)select @s=isnull(@s+'_','') +typeName
from tb join @p b
on tb.typeCod=b.id order by [level]
RETURN @s
ENDselect dbo.f_Pid('KM0019')----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
二级_一级_国家_ 大类
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-28 10:32:46
if not object_id('tb') is null
drop table tb
Go
Create table tb([typeCode] nvarchar(6),[typeName] nvarchar(2),[parentCode] nvarchar(6))
Insert tb
select N'KM4832',N'大类',N'0' union all
select N'KM0017',N'国家',N'KM4832' union all
select N'KM0018',N'一级',N'KM0017' union all
select N'KM0019',N'二级',N'KM0018'
--Go
if object_id('fun_test')is not null drop function fun_test
go
create function fun_test(@pp nvarchar(200))
returns nvarchar(50)
as
begin
declare @p varchar(10)
set @p=@pp
declare @s nvarchar(1000)
while (@@rowcount>0)
begin
select @p=[parentCode],@s=isnull(@s+'_','')+[typeName] from tb where [typeCode]=@p
end
return @s
end
go
select dbo.fun_test('KM0019')
/*
--------------------------------------------------
二级_一级_国家_大类(1 個資料列受到影響)
*/
create table testtable (typeCode varchar(255),typeName varchar(255),parentCode varchar(255))
go--创建测试数据
insert into testtable select 'KM4832','大类','0'
union all select 'KM0017','国家','KM4832'
union all select 'KM0018','一级','KM0017'
union all select 'KM0019','二级','KM0018'
go--创建递归函数
create function f_testtable (@typecode_temp varchar(255))
returns varchar(255)
as
begin
declare @returnstr varchar(255),@parentcode_temp varchar(255),@tempstr varchar(255)
set @returnstr = ''
set @parentcode_temp = ''
set @tempstr = ''
select @returnstr=typename,@parentcode_temp=parentcode from testtable where typecode = @typecode_temp
while exists (select 1 from testtable where typecode = @parentcode_temp)
begin
set @returnstr = @returnstr + '_'
select @tempstr=typename,@parentcode_temp=parentcode from testtable where typecode = @parentcode_temp
set @returnstr = @returnstr + @tempstr
end
return @returnstr
end--测试查询
select dbo.f_testtable ('KM0019')--得到测试结果:二级_一级_国家_大类