34,593
社区成员
发帖
与我相关
我的任务
分享
create table test
(ID char(3),
Name char(20),
PID int) --PID为0,表示根节点,其他表示最近对应根节点下的子节点
insert into test values
('1','性别',0),
('2','男',1),('3','女',1),
('4','状态',0),
('5','是',4),('6','否',4),
('7','状态1',0),
('8','是',8),('9','否',8)
select a.ID,case when a.PID=0 THEN a.Name ELSE
(select top 1 b.Name from test b where b.ID<=a.ID and b.PID=0 order by B.ID desc) END AS Name,
CASE WHEN PID=0 THEN NULL ELSE Name END as value,PID from test a
if object_id('myuser') is not null
drop table myuser
go
create table myuser(
ID int,
Name varchar(30),
PID int
)
go
insert into myuser(ID,Name,PID)
select 1,'性别',0 union all
select 2,'男',1 union all
select 3,'女', 1 union all
select 4,'状态 ',0 union all
select 5,'是',4 union all
select 6,'否',4
;with
t as (
select ID,Name from myuser where PID =0
)
select B.ID,isnull((select name from t where t.Id = B.PID),B.Name) as Name
,(case (select count(*) from t where t.Name = B.Name) when 1 then null else B.Name end) value,B.PID from myuser as B
/*
-------------result --------------------
ID Name value PID
1 性别 NULL 0
2 性别 男 1
3 性别 女 1
4 状态 NULL 0
5 状态 是 4
6 状态 否 4
*/