SQL 树形菜单查询,查询本身和所有下级项
WITH Temp AS
(
SELECT * FROM Sys_Organization WHERE OUGUID = @OUGUID AND OrgState = 1 AND IsAvailable = 1 AND IsDel = 0
UNION ALL
SELECT A.* FROM Sys_Organization A INNER JOIN Temp B
ON A.OUGUID = B.ParentOUGUID AND A.OrgState = 1 AND A.IsAvailable = 1 AND A.IsDel = 0
)
SELECT DISTINCT * FROM Temp ORDER BY OULevel
函数
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: HZ
-- Create date: 2011/10/17
-- Description: 传入字典类别,字典编码得到该级及以下类型的字典编码
-- =============================================
ALTER FUNCTION [dbo].[GetCodeValueDetail]
(
@CodeFlag nvarchar(20),
@CodeValue nvarchar(20)
)
RETURNS @table table(CodeValue nvarchar(20))
AS
begin
declare @IncludeItemCount int
declare @ParentCode nvarchar(20)
declare @Table_ParentID table(ParentCode nvarchar(20))
declare @Table_IParentID table(IParentCode nvarchar(20))
declare @Table_Mid table(ParentCode nvarchar(20))
declare CursorCodeValue cursor for
select distinct CodeValue from dbo.Sys_CodeValueDetail
where CodeValue= @CodeValue and CodeFlag=@CodeFlag
open CursorCodeValue
fetch next from CursorCodeValue into @ParentCode
while @@fetch_status = 0
begin
insert into @Table_ParentID values (@ParentCode)
insert into @Table_IParentID values (@ParentCode)
select @IncludeItemCount = count(a.CodeValue)
from Sys_CodeValueDetail a,@Table_IParentID b
where a.ParentCode = b.IParentCode
while (@IncludeItemCount > 0)
begin
insert into @Table_ParentID
select distinct a.CodeValue
from Sys_CodeValueDetail a , @Table_IParentID b
where a.ParentCode = b.IParentCode
delete @Table_Mid
insert into @Table_Mid
select * from @Table_IParentID
delete @Table_IParentID
insert into @Table_IParentID
select distinct a.CodeValue
from Sys_CodeValueDetail a,@Table_Mid b
where a.ParentCode = b.ParentCode
select @IncludeItemCount = count(a.CodeValue)
from Sys_CodeValueDetail a,@Table_IParentID b
where a.ParentCode = b.IParentCode
end
delete @Table_IParentID
delete @Table_Mid
fetch next from CursorCodeValue into @ParentCode
end
close CursorCodeValue
deallocate CursorCodeValue
insert into @Table_Mid select distinct * from @Table_ParentID
insert into @table select * from @Table_Mid
return
end