日期:2014-05-17 浏览次数:20691 次
declare @test table(一级 varchar(1), 二级 varchar(1), 三级 varchar(1)) insert into @test select 'a', 'b', 'e' union all select 'a', 'f', '' union all select 'c', 'd', 'x' union all select 'c', 'h', '' ;with cte as ( select row_number() over(order by 一级) rn,* from @test ) ,t as ( select row_number() over(order by rn,级别)id,级别,值 from ( select rn,一级 as 值,1 级别 from cte union all select rn,二级,2 from cte union all select rn,三级,3 from cte )t where 值<>'' ) select 级别,值 from t a where not exists(select 1 from t where a.id>id and 级别=a.级别 and 值=a.值) order by id /* 级别 值 ----------- ---- 1 a 2 b 3 e 2 f 1 c 2 d 3 x 2 h */