mysql 通用树表_通用树形表查询SQL

--=============================================----Description:    获取当前表所选分类ID下的所有下级分类ID--=============================================ALTERPROC[dbo].[GetTreeTable](@IDint=null,--要查询的ID，查询所有输入NULL@TableNamevarchar(100),--要查询的表名@IDNamevarchar(200),--主字段名@SuperIDNamevarchar(200)--上级字段名)ASBEGIN--create table #Tab (ID int, SuperID int, Lev int)--Declare @Lev intDECLARE@StrSQLvarchar(5000)--Set @Lev=0--

--While @Lev=0 or @@ROWCount>0--Begin--Set @Lev=@Lev+1--SET @StrSQL = 'Insert #Tab(ID, SuperID, Lev) Select '+@IDName+', '+@SuperIDName+', '+convert(varchar(10),@Lev)+' From '+@TableName+' Where ('+convert(varchar(10),@Lev)+'=1 and (('+@IDName+'='+convert(varchar(10),@ID)+') or (('+convert(varchar(10),@ID)+' is null or '+convert(varchar(10),@ID)+'='''') and '+@SuperIDName+' is null))) or ('+@SuperIDName+' in (Select ID From #Tab Where Lev='+convert(varchar(10),@Lev)+'-1)) order by '+@IDName----print @StrSQL--EXEC (@StrSQL)--EndSET@StrSQL='WITH DirectReports('+@IDName+','+@SuperIDName+',Level) AS

(

SELECT'+@IDName+','+@SuperIDName+', 0 AS Level

FROM'+@TableName+'WHERE ('+@IDName+'='+CONVERT(VARCHAR(100),ISNULL(@ID,''))+') OR ('+@SuperIDName+'IS NULL AND'+CONVERT(VARCHAR(100),ISNULL(@ID,''))+'= 0)

UNION ALL

SELECT e.'+@IDName+', e.'+@SuperIDName+', Level + 1

FROM'+@TableName+'e

INNER JOIN DirectReports d

ON e.'+@SuperIDName+'= d.'+@IDName+')

SELECT'+@IDName+','+@SuperIDName+',Level

FROM DirectReports

ORDER BY Level'EXEC(@StrSQL)PRINT@StrSQL--select * from #Tab--drop table #TabEND--exec [GetTreeTable]    1060,'T_Department','DepID','SuperDepID'--exec [GetTreeTable]    NULL,'T_WLFLDY','WLFLID','SJFL'--SELECT * FROM T_WLFLDY