有2张表 hr_department (部门表 有上下级关系) t_deptsale(部门销售表,里面有对应的部门ID)
-----
查询上下级方式的话 我原来用的函数为
ALTER FUNCTION [dbo].[f_getDepParID] (@ID int)
RETURNS @t_Level TABLE(ID int,[Level] int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.parDepartment,@Level
FROM hr_department a,@t_Level b
WHERE a.departmentID=b.ID
AND b.Level=@Level-1
END
delete from @t_Level where ID=0
RETURNEND现在我想用一句sql 写出 这样的查询结果
deptID 在 部门销售表(t_deptsale)中,并且如果子部门有在t_deptsale中,刚子部门的所有上级部门也要查出来
-----
查询上下级方式的话 我原来用的函数为
ALTER FUNCTION [dbo].[f_getDepParID] (@ID int)
RETURNS @t_Level TABLE(ID int,[Level] int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.parDepartment,@Level
FROM hr_department a,@t_Level b
WHERE a.departmentID=b.ID
AND b.Level=@Level-1
END
delete from @t_Level where ID=0
RETURNEND现在我想用一句sql 写出 这样的查询结果
deptID 在 部门销售表(t_deptsale)中,并且如果子部门有在t_deptsale中,刚子部门的所有上级部门也要查出来
RETURNS @t_Level TABLE(ID int,[Level] int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.parDepartment,@Level
FROM hr_department a,@t_Level b
WHERE a.ID=b.departmentID
AND b.Level=@Level-1
END
delete from @t_Level where ID=0
RETURNEND