if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[mc] varchar(10),[parent] int) insert [tb] select 1,'生产部',5 union all select 2,'拉丝',1 union all select 3,'退火',1select b.mc mcz,a.mc from tb a join tb b on a.parent=b.id /* mcz mc ---------- ---------- 生产部 拉丝 生产部 退火(2 行受影响) */
DECLARE @TB TABLE([id] INT, [mc] NVARCHAR(3), [parent] INT) INSERT @TB SELECT 1, N'生产部', 5 UNION ALL SELECT 2, N'拉丝', 1 UNION ALL SELECT 3, N'退火', 1SELECT mcz=(SELECT mc FROM @TB WHERE id=T.parent),mc FROM @TB T WHERE parent=1
declare @mkz table([id] int, mc nvarchar(10), parent int) insert @mkz select 1, N'生产部', 5 union all select 2, N'拉丝', 1 union all select 3, N'退火', 1select B.mc as mcz, A.mc from @mkz A left outer join @mkz B on A.parent = B.[id] where A.parent = 1 --select B.mc as mcz, A.mc from mkz A left outer join mkz B on A.parent = B.[id] where A.parent = 1
go
create table [tb]([id] int,[mc] varchar(10),[parent] int)
insert [tb] select 1,'生产部',5
union all select 2,'拉丝',1
union all select 3,'退火',1select b.mc mcz,a.mc from tb a join tb b on a.parent=b.id
/*
mcz mc
---------- ----------
生产部 拉丝
生产部 退火(2 行受影响)
*/
INSERT @TB
SELECT 1, N'生产部', 5 UNION ALL
SELECT 2, N'拉丝', 1 UNION ALL
SELECT 3, N'退火', 1SELECT mcz=(SELECT mc FROM @TB WHERE id=T.parent),mc
FROM @TB T
WHERE parent=1
insert @mkz select 1, N'生产部', 5
union all select 2, N'拉丝', 1
union all select 3, N'退火', 1select B.mc as mcz, A.mc from @mkz A left outer join @mkz B on A.parent = B.[id] where A.parent = 1
--select B.mc as mcz, A.mc from mkz A left outer join mkz B on A.parent = B.[id] where A.parent = 1
不知道思路正确不,目前正在学
SQL