【IT168技术文档】
无限分级的数据查询是个头痛的问题，递归查询类别，再组合成字符串，用 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";
