你的顺序不对吧: create table tb(tname varchar(10), ttype varchar(10)) insert tb select '库存现金','' union all select '存现金','库存现金' union all select '现金','存现金' union all select '金','现金' go --2005 ;with cte as ( select convert(varchar(100),tname)tname,ttype from tb where tname = '金' union all select convert(varchar(100),b.tname+'-'+a.tname),a.ttype from tb a join cte b on a.tname = b.ttype ) select max(tname)tname from cte /* tname ---------------------------------------------------------------------------------------------------- 金-现金-存现金-库存现金(1 行受影响) */ go drop table tb
or: create table tb(tname varchar(10), ttype varchar(10)) insert tb select '库存现金','' union all select '存现金','库存现金' union all select '现金','存现金' union all select '金','现金' go --2005 ;with cte as ( select convert(varchar(100),tname)tname,ttype from tb where tname = '金' union all select convert(varchar(100),a.tname+'-'+b.tname),a.ttype from tb a inner join cte b on a.tname = b.ttype ) select tname from cte a where not exists(select 1 from cte where len(tname)>len(a.tname))/* tname ---------------------------------------------------------------------------------------------------- 库存现金-存现金-现金-金(1 行受影响)*/ go drop table tb
create table tb(tname varchar(10), ttype varchar(10))
insert tb
select '库存现金','' union all
select '存现金','库存现金' union all
select '现金','存现金' union all
select '金','现金'
go
--2005
;with cte as
(
select convert(varchar(100),tname)tname,ttype from tb where tname = '金'
union all
select convert(varchar(100),b.tname+'-'+a.tname),a.ttype from tb a join cte b on a.tname = b.ttype
)
select max(tname)tname from cte
/*
tname
----------------------------------------------------------------------------------------------------
金-现金-存现金-库存现金(1 行受影响)
*/
go
drop table tb
create table tb(tname varchar(10), ttype varchar(10))
insert tb
select '库存现金','' union all
select '存现金','库存现金' union all
select '现金','存现金' union all
select '金','现金'
go
--2005
;with cte as
(
select convert(varchar(100),tname)tname,ttype from tb where tname = '金'
union all
select convert(varchar(100),a.tname+'-'+b.tname),a.ttype from tb a inner join cte b on a.tname = b.ttype
)
select tname from cte a where not exists(select 1 from cte where len(tname)>len(a.tname))/*
tname
----------------------------------------------------------------------------------------------------
库存现金-存现金-现金-金(1 行受影响)*/
go
drop table tb