tab1id kindnum kindname1 001 男装2 001001 上衣3 001002 裤类4 001001001 西装5 002 女装6 001001002 风衣7 002001 帽8 002002 上衣9 002003 裙我现在想实现查询出来的显示结果如下:id kindnum kindname full_kindname1 001 男装 男装2 001001 上衣 男装-上衣3 001002 裤类 男装-裤类4 001001001 西装 男装-上衣-西装6 001001002 风衣 男装-裤类-风衣5 002 女装 女装7 002001 帽 女装-帽8 002002 上衣 女装-上衣9 002003 裙 女装-裙如何写这个SQL语句呢?——————————————————————————————
if object_id('[tab1]') is not null drop table [tab1]gocreate table [tab1]([id ] int,kindnum varchar(20),kindname varchar(20))insert [tab1]select 1 ,'001', '男装' union allselect 2 ,'001001', '上衣' union allselect 3 ,'001002', '裤类' union allselect 4 ,'001001001', '西装' union allselect 5 ,'002', '女装' union allselect 6 ,'001001002', '风衣' union allselect 7 ,'002001', '帽' union allselect 8 ,'002002', '上衣' union allselect 9 ,'002003', '裙'
go
if object_id('f_getk') is not null drop function f_getk
create function f_getk(@kindname varchar(20)) returns varchar(100)as begin declare @k_full varchar(300) declare @i int set @i =1 while len(@kindname)>= 3*@i begin select @k_full=isnull(@k_full+'-','')+kindname from tab1 where kindnum = left(@kindname,3*@i) set @i = @i+1 end return @k_fullend
select * ,dbo.f_getk(kindnum) as 'full_kname' from tab1
