[推薦]無限分級數量查詢優化

無限分級的資料查詢是個頭痛的問題，遞迴查詢類別，再組合成字串，用 in 來解決子類所有產品的問題，但是這個效率太低，低的讓人無法接受，在此，有一個SQL的方法，可讓我們提高效率。
-----提取子類的所有類別ID
create function GetChild (@id int)
returns @t table(id int)
as
begin
    insert @t select classid from mproclass where parentid = @id
    while @@rowcount > 0
        insert @t select a.classid from mproclass as a inner join @t as b
        on a.parentid = b.id and a.classid not in(select id from @t)
   return
end
-----提取子類以及自己ID的所有類別ID
create function GetChildAndSelf (@id int)
returns @t table(id int)
as
begin
    insert @t values (@id)
    insert @t select classid from mproclass where parentid = @id
    while @@rowcount > 0
        insert @t select a.classid from mproclass as a inner join @t as b
        on a.parentid = b.id and a.classid not in(select id from @t)
   return
end

以上是最佳方案

查詢該類別的產品資料的sql為：
sql = "select * from mProduct as a inner join (select [id] from GetChildAndSelf("+classid+")) as b on a.classid=b.id order by psortid desc,pdate desc";

太爽了！

網上還有其他幾種方法，貼出來大家一起學習學習

一、
declare   @table   table(id   int,upperid   int)
insert   into   @table
select   1,           2
union   all   select   3,           2
union   all   select   4,           1
union   all   select   5,           3
declare   @upperid   int  
set   @upperid=2;
with   result(id,upperid)
as
(
select   id,upperid   from   @table   where   upperid=@upperid
union   all
select   a.id,a.upperid   from   @table   a   inner   join   result   b   on   a.upperid=b.id
)
select*from   result
/*
id                     upperid
-----------   -----------
1                       2
3                       2
5                       3
4                       1

(4   row(s)   affected)
*/

二、
Create   table   t(id   int,upperid   int)
insert   into   t
select   1,           2
union   all   select   3,           2
union   all   select   4,           1
union   all   select   5,           3
select   *   from   t
create   function   aa(@upperid   int)
returns   @t   table   (id   int,upperid   int,level   int)
as
begin
declare   @i   int
set   @i=1
insert   into   @t
select   *,@i   from   t   where   upperid=@upperid
while   @@rowcount> 0
begin
set   @i=@i+1
insert   into   @t
select   a.*,@i   from   t   a   left   join   @t   b   on   a.upperid=b.id
where   b.level=@i-1
end
return
end

select   *   from   dbo.aa(1)

id                     upperid           level              
-----------   -----------   -----------  
4                       1                       1

（所影響的行數為   1   行）

select   *   from   dbo.aa(2)

id                     upperid           level              
-----------   -----------   -----------  
1                       2                       1
3                       2                       1
4                       1                       2
5                       3                       2


三、
----建立測試資料
if   object_id( 'tbTest ')   is   not   null
drop   table   tbTest
if   object_id( 'spGetChildren ')   is   not   null
drop   proc   spGetChildren
GO
create   table   tbTest(id   int,     upperid   int)
insert   tbTest
select   1,           2   union   all
select   3,           2   union   all
select   4,           1   union   all
select   5,           3
GO
----建立儲存過程
create   proc   spGetChildren   @id   int
as
        declare   @t   table(id   int)
        insert   @t   select   id   from   tbTest   where   upperid   =   @id
        while   @@rowcount   >   0
                insert   @t   select   a.id   from   tbTest   as   a   inner   join   @t   as   b
                on   a.upperid   =   b.id   and   a.id   not   in(select   id   from   @t)
        select   *   from   @t
GO

----執行儲存過程
declare   @upperid   int
set   @upperid   =   2
EXEC   spGetChildren   @upperid

----清除測試環境
drop   proc   spGetChildren
drop   table   tbTest

/*結果
id                    
-----------  
1
3
4
5
*/