if object_id('[tab]') is not null drop table [tab] create table [tab]([id] int,[品种] varchar(3),[pid] int) insert [tab] select 1,'1级',0 union all select 2,'2级',1 union all select 3,'3级',2select * from [tab] select id,品种,p_pz=(case when pid=0 then null else cast(pid as varchar(10))+'级' end), pid from tab /* id 品种 p_pz pid ----------- ---- ------------ ----------- 1 1级 NULL 0 2 2级 1级 1 3 3级 2级 2(所影响的行数为 3 行) */
CREATE TABLE TBTEST(id INT,品种 VARCHAR(20), pid INT) INSERT TBTESTSELECT 1 , '1级' , 0 UNION ALL SELECT 2 , '2级' , 1 UNION ALL SELECT 3 , '3级' , 2 SELECT ID,品种 AS PZ,NEWPZ=(SELECT 品种 FROM TBTEST WHERE T.PID=ID),PID FROM TBTEST TID PZ NEWPZ PID ----------- -------------------- -------------------- ----------- 1 1级 NULL 0 2 2级 1级 1 3 3级 2级 2(所影响的行数为 3 行)
create table parenttest (id int,pz varchar(5),pid int)insert parenttest select 1,'1级',0 union all select 2,'2级',1 union all select 3,'3级',2select a.id,a.pz,b.pz as p_pz,a.pid from parenttest as a left join parenttest b on a.pid=b.id
create table [tab]([id] int,[品种] varchar(3),[pid] int)
insert [tab]
select 1,'1级',0 union all
select 2,'2级',1 union all
select 3,'3级',2select * from [tab]
select id,品种,p_pz=(case when pid=0 then null else cast(pid as varchar(10))+'级' end),
pid
from tab
/*
id 品种 p_pz pid
----------- ---- ------------ -----------
1 1级 NULL 0
2 2级 1级 1
3 3级 2级 2(所影响的行数为 3 行)
*/
INSERT TBTESTSELECT 1 , '1级' , 0 UNION ALL
SELECT 2 , '2级' , 1 UNION ALL
SELECT 3 , '3级' , 2 SELECT ID,品种 AS PZ,NEWPZ=(SELECT 品种 FROM TBTEST WHERE T.PID=ID),PID FROM TBTEST TID PZ NEWPZ PID
----------- -------------------- -------------------- -----------
1 1级 NULL 0
2 2级 1级 1
3 3级 2级 2(所影响的行数为 3 行)
select 1,'1级',0 union all
select 2,'2级',1 union all
select 3,'3级',2select a.id,a.pz,b.pz as p_pz,a.pid
from parenttest as a left join parenttest b
on a.pid=b.id