求一存储过程 关于网上商城的

Question

要求按照类别查询商品,
类别表形为
id parentid desn
1     0    体育用品
2     0    户外运动 
3     1     篮球
4     1     足球
5     2     帐篷
6     2     登山鞋
7     0     男士用品
8     7     刮胡刀
9     3     大号篮球
......
这个类别表是无限级多层关系的
还一个商品表,其中商品表有个类别表的id外键,记录此商品属于类别表中的最终的一个类别,
现在就是想知道一个存储过程,我选择类别表其中的一个节点,能够找到属于此节点最后一层的类别的所有商品,并且可以带各种条件的(比如按添加时间,价格排序等)
请各位大哥大姐帮帮忙~如果还是不清楚我说的话可以参考一下淘宝跟拍拍的搜索那样的哈,谢谢了!在线等候!!!!!!

Answer 1

参考:http://blog.csdn.net/zjcxc/category/125593.aspx

Answer 2

create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'--首先查找该节点下所有子接点
create function f_cid(@id int)
returns varchar(500)
as
begin
     declare @t table(id int,parentid int,desn varchar(10),lev int)
     declare @lev int
     set @lev=1
     insert into @t select *,@lev from  os where id=@id
     while(@@rowcount>0)
     begin
          set @lev=@lev+1
          insert into @t select a.*,@lev from os a,@t b
          where a.parentid=b.id and b.lev=@lev-1
     end
     declare @cids varchar(500)
     select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
     return @cids
end
--查询属于该类别以及类别下所有子节点的商品
create proc wsp
@id int
as
     select * from 商品表 
     where charindex(','+ltrim(商品类别id)+',',','+dbo.f_cid(@id)+',')>0
    order by 添加时间,价格 --调用存储过程
exec wsp 1

Answer 3

--这个效率应该会高点：
create table os(id int,parentid int,desn varchar(10))
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'
create proc wsp
@id int
as
select *,cast(' ' as varchar(10)) fullpath  into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id 
while @j<=@i
begin
   update #os set fullpath=a.fullpath+','+ltrim(#os.id) 
from #os inner join #os a on #os.parentid=a.id 
   where #os.parentid=@j 
   set @j=@j+1
end
select a.* from 商品表 a,#os b
where charindex(','+ltrim(商品类别id)+',',','+b.fullpath+',')>0
and b.id=@id
order by 添加时间,价格--调用存储过程
exec wsp 1

Answer 4

if object_id('类别表') is not null
   drop table 类别表
if object_id('商品表') is not null
   drop table 商品表
go
create table 类别表(id int identity(1,1),parentid int,desn varchar(100))
insert into 类别表
select 0,'体育用品' union all
select 0,'户外运动' union all
select 1,'篮球' union all
select 1,'足球' union all
select 2,'帐篷' union all
select 2,'登山鞋' union all
select 0,'男士用品' union all
select 7,'刮胡刀' union all
select 3,'大号篮球'
create table 商品表(id int identity(1,1),Cid int,name_ch varchar(100))
insert into 商品表
select 6,'登山鞋1' union all
select 9,'大号篮球鞋1' union all
select 8,'刮胡刀1' union all
select 4,'足球1' union all
select 5,'帐篷1'
go
if object_id('f_getDown') is not null
    drop function f_getDown
go
create function f_getDown(@id int)
returns @t table(childid int,des varchar(100),level_i int)
as
begin
   declare @level int
   set @level=1
   insert into @t select id,desn,@level from 类别表 where parentid=@id
   while(@@rowcount>0)
      begin
         set @level=@level+1
         insert into @t 
         select a.id,a.desn,@level from 类别表 a ,@t b 
         where a.parentid=b.childid and b.level_i=@level-1
      end
   return
end
go
select b.id as [商品ID],b.name_ch as [商品名称],a.childid as [类别id],des as [类别名称]
from
(
select * from dbo.f_getDown(1) 
) a
inner join 
商品表 b
on b.Cid=a.childid
--
跟帖子
接分啊

Answer 5

http://blog.csdn.net/roy_88/archive/2008/01/15/2045842.aspx
例子

Answer 6

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,parentid int,desn varchar(8))
insert into #T
select 1,0,'体育用品' union all
select 2,0,'户外运动' union all
select 3,1,'篮球' union all
select 4,1,'足球' union all
select 5,2,'帐篷' union all
select 6,2,'登山鞋' union all
select 7,0,'男士用品' union all
select 8,7,'刮胡刀' union all
select 9,3,'大号篮球'--> proc parameters
declare @id int
set @id = 1 --> 选择类别表其中的一个节点--> proc body
declare @Level int
set @Level = 0
if object_id('tempdb.dbo.#') is not null drop table #
select *, Level = @Level into # from #T where id = @id
while @@rowcount>0
begin
set @Level = @Level + 1
insert # select a.*, @Level from #T a join # b on a.parentid = b.id where b.Level = @Level-1
end
--> 将该节点下属的所有商品列出
select * from # order by 1
/*
id          parentid    desn     Level
----------- ----------- -------- -----------
1           0           体育用品 0
3           1           篮球     1
4           1           足球     1
9           3           大号篮球 2
*/
--> 这个#表与商品表关联，查找数据：
select * from # join 商品表 t on #.id = t.id where other_proc_parameters

Answer 7

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,parentid int,desn varchar(8))
insert into #T
select 1,0,'体育用品' union all
select 2,0,'户外运动' union all
select 3,1,'篮球' union all
select 4,1,'足球' union all
select 5,2,'帐篷' union all
select 6,2,'登山鞋' union all
select 7,0,'男士用品' union all
select 8,7,'刮胡刀' union all
select 9,3,'大号篮球'--> proc parameters
declare @id int
set @id = 2 --> 选择类别表其中的一个节点
;
--> 2005 proc body
with Class as
(
select * from #T where id = @id
union all
select a.* from #T a join Class b on a.parentid = b.id
)
select * from Class a --join 商品表 b on a.id = b.id where other_proc_parameters
/*
id          parentid    desn
----------- ----------- --------
2           0           户外运动
5           2           帐篷
6           2           登山鞋
*/

Answer 8

好多热情的朋友啊～我在解释一次吧，好多朋友好像没有领会我的意思呢
类别表已经列出，现在就是想选择其中一个节点（任意一个），我在类别表多加一些，
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
id parentid desn 
1     0    体育用品 
2     0    户外运动  
3     1     篮球 
4     1     足球 
5     2     帐篷 
6     2     登山鞋 
7     0     男士用品 
8     7     刮胡刀 
9     3     大号篮球 
10    3     小号篮球
11    3     牛皮篮球
12    3     儿童篮球
13    4     大号足球
14    13    阿迪达斯足球
...........
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
比方说体育用品，我想找到属于此类别的最底一层的所有属于此类别的节点，那么这些节点应该就是9，10，11，12，14，
注意，这些节点是没有支节点了，也就是说这些节点都是1号跟节点的最后一个节点，另外，体育用品这个节点还可能包括其他节点，太多我就不列举了，现在就是不论我是点了体育用品（1）节点，还是点了男士用品(7)节点，都能按照此节点，找到此节点下的最后一层的节点，谢谢！
看我说的一大堆，头脑晕的人可以借鉴一下淘宝那样的搜索哈，谢谢！！！

Answer 9

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,parentid int,desn varchar(12))
insert into #T
select 1,0,'体育用品' union all
select 2,0,'户外运动' union all
select 3,1,'篮球' union all
select 4,1,'足球' union all
select 5,2,'帐篷' union all
select 6,2,'登山鞋' union all
select 7,0,'男士用品' union all
select 8,7,'刮胡刀' union all
select 9,3,'大号篮球' union all
select 10,3,'小号篮球' union all
select 11,3,'牛皮篮球' union all
select 12,3,'儿童篮球' union all
select 13,4,'大号足球' union all
select 14,13,'阿迪达斯足球'--> proc parameters
declare @id int
set @id = 1 --> 选择类别表其中的一个节点--> proc body
declare @Level int
set @Level = 0
if object_id('tempdb.dbo.#') is not null drop table #
select *, Level = @Level into # from #T where id = @id
while @@rowcount>0
begin
    set @Level = @Level + 1
    insert # select a.*, @Level from #T a join # b on a.parentid = b.id where b.Level = @Level-1
end
delete a from # a where exists (select 1 from # where parentid=a.id)
select * from #
/*
id          parentid    desn         Level
----------- ----------- ------------ -----------
9           3           大号篮球         2
10          3           小号篮球         2
11          3           牛皮篮球         2
12          3           儿童篮球         2
14          13          阿迪达斯足球     3
*/

Answer 10

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,parentid int,desn varchar(12))
insert into #T
select 1,0,'体育用品' union all
select 2,0,'户外运动' union all
select 3,1,'篮球' union all
select 4,1,'足球' union all
select 5,2,'帐篷' union all
select 6,2,'登山鞋' union all
select 7,0,'男士用品' union all
select 8,7,'刮胡刀' union all
select 9,3,'大号篮球' union all
select 10,3,'小号篮球' union all
select 11,3,'牛皮篮球' union all
select 12,3,'儿童篮球' union all
select 13,4,'大号足球' union all
select 14,13,'阿迪达斯足球'--> proc parameters
declare @id int
set @id = 1 --> 选择类别表其中的一个节点--> proc body
declare @Level int
set @Level = 0
if object_id('tempdb.dbo.#') is not null drop table #
select *, Level = @Level into # from #T where id = @id
while @@rowcount>0
begin
    set @Level = @Level + 1
    insert # select a.*, @Level from #T a join # b on a.parentid = b.id where b.Level = @Level-1
end
--delete a from # a where exists (select 1 from # where parentid=a.id)
select * from # a where not exists (select 1 from # where parentid=a.id)
/*
id          parentid    desn         Level
----------- ----------- ------------ -----------
9           3           大号篮球         2
10          3           小号篮球         2
11          3           牛皮篮球         2
12          3           儿童篮球         2
14          13          阿迪达斯足球     3
*/

Answer 11

create table ta (id int,parentid int,desn varchar(12))
insert into ta
select 1,0,'体育用品' union all
select 2,0,'户外运动' union all
select 3,1,'篮球' union all
select 4,1,'足球' union all
select 5,2,'帐篷' union all
select 6,2,'登山鞋' union all
select 7,0,'男士用品' union all
select 8,7,'刮胡刀' union all
select 9,3,'大号篮球' union all
select 10,3,'小号篮球' union all
select 11,3,'牛皮篮球' union all
select 12,3,'儿童篮球' union all
select 13,4,'大号足球' union all
select 14,13,'阿迪达斯足球'
go--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int
    set @i = 1
    insert into @t select ID,parentid,@i from ta where ID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.parentid,@i 
        from 
            ta a,@t b 
        where 
            a.parentid=b.ID and b.Level = @i-1
    end
    delete  a
    from @t a
    where  exists(select 1 from @t where pid = a.id)
    return
end
goselect a.* 
from ta a,dbo.f_getChild(1) b
where a.id = b.id
drop function f_getChild
drop table ta
/*
id          parentid    desn         
----------- ----------- ------------ 
9           3           大号篮球
10          3           小号篮球
11          3           牛皮篮球
12          3           儿童篮球
14          13          阿迪达斯足球（所影响的行数为 5 行）
*/

Answer 12

http://blog.csdn.net/zjcxc/category/125593.aspx
参考这个自己学着写才能进步 不建议 直接拷贝别人写好的语句

Answer 13

不好意思 看到了各位给的例子基本都差不多我就按照了happyflystone 朋友的方法试了一下
可是不好使啊~不知道是不是我搞错了
类别表是无限级分类其实最多可能也就是5,6层了  我在"阿迪达斯足球"下面在加一层,就出错拉...

Answer 14

是不是我的类别表的CategoryID是自增列的关系?我这里试验的怎么只能找到第一层就不往下找了??其他都对啊

Answer 15

应该使用的是sql的嵌套查询吧，向强人们学习