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

無限分級的資料查詢是個頭痛的問題，遞迴查詢類別，再組合成字串，用 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
*/

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

相關文章