--作者:振乾
--环境: sql server 2008 R2
IF(OBJECT_ID('Company') IS not NULL)
DROP TABLE Company
CREATE TABLE Company
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(10),
ParentID INT,
Re text
)
INSERT Company VALUES
('xx公司',0,''),
('研发部',1,''),
('销售部',1,''),
('张三',2,''),
('李四',3,''),
('王五',3,''),
('赵六',2,'')
SELECT * FROM company--我的代码(正常通过)
declare @str varchar(100)
SET @str=''
;WITH CompanyStruct
AS
(
SELECT *,CAST(NULL AS VARCHAR(10)) AS ParentName FROM company WHERE id=2
UNION ALL
SELECT cp.*,CompanyStruct.name FROM company cp
INNER JOIN CompanyStruct ON cp.id=CompanyStruct.parentid
)
--SELECT * FROM CompanyStruct
SELECT @str=@str+'-'+NAME FROM companystruct ORDER BY parentid
PRINT STUFF(@str,1,1,'') --比如 我查询 ID=7 的 张三 ,找到他上级所有的名称
最终的效果是:
xx公司-研发部
-----疑问:
大家有没有更简洁,更方便的代码来实现呢,请各位大牛多多指教!
drop table [Tree]
go
CREATE TABLE [dbo].[Tree](
[ID] [bigint] identity,
[Parent] as (ID-1),
[Name] as ('Name'+rtrim(ID))
)
go
declare @i int
set @i=35
while @i>0
begin
insert [tree] default values
set @i=@i-1
end
--生成格式:
/*
ID Parent Name
-------------------- -------------------- ----------------------------
1 0 Name1
2 1 Name2
3 2 Name3
4 3 Name4
5 4 Name5
6 5 Name6
7 6 Name7
8 7 Name8
9 8 Name9
10 9 Name10
................................................................................................
31 30 Name31
32 31 Name32
33 32 Name33
34 33 Name34
35 34 Name35*/
go
if object_id('F_BOM','FN') is not null
drop function F_BOM
go
create function F_BOM(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000),@Name nvarchar(20)
lab:
set @Name =(select Name from Tree where ID=@ID)
select @ID=Parent from Tree where ID=@ID
if @Name is not null
begin
set @s=@Name+isnull('-'+@s,'')
goto lab
end
return @s
end
go
if object_id('F_BOM2','FN') is not null
drop function F_BOM2
go
create function F_BOM2(@ID int)
returns nvarchar(1000)
as
begin
declare @s nvarchar(1000)
while exists(select 1 from Tree where ID=@ID)
select @s=Name+isnull('-'+@s,''),@ID=Parent from Tree where ID=@ID
return @s
end
go