表结构数据:(goods)
a(id=2)
|-b
|-c
|-e
|-f
|-d
|-e
|-g
|-h
|-j
|-k
q(id=3)
|-w
|-r
|-t
|-y
z(id=4)
|-v
|-n
|-m
|-x
==============
分类表:
categoryid int identity
categoryname
fatherid int
=============
(商品表中分类字段名是categoryid)
1、以ID为2的a为例,如果用一条语句查询下面所有分类的商品,商品所属分类有可能设置c/e/f这些三级分类上,也有可能是在b/d/g这些二级分类上
2、在1的基础上如何同时查询ID为3、4下所有分类商品select * from goods where categoryid in (select categoryid from category where fatherid=2)
起先我想用类似这样的语句来查询,但现在有的商品可能会在二级,有的在三级,所以上面的语句可能有点问题
a(id=2)
|-b
|-c
|-e
|-f
|-d
|-e
|-g
|-h
|-j
|-k
q(id=3)
|-w
|-r
|-t
|-y
z(id=4)
|-v
|-n
|-m
|-x
==============
分类表:
categoryid int identity
categoryname
fatherid int
=============
(商品表中分类字段名是categoryid)
1、以ID为2的a为例,如果用一条语句查询下面所有分类的商品,商品所属分类有可能设置c/e/f这些三级分类上,也有可能是在b/d/g这些二级分类上
2、在1的基础上如何同时查询ID为3、4下所有分类商品select * from goods where categoryid in (select categoryid from category where fatherid=2)
起先我想用类似这样的语句来查询,但现在有的商品可能会在二级,有的在三级,所以上面的语句可能有点问题
select a.* from tb a inner join dbo.f_getchildidsort(2) b on a.id=b.id order by b.sortid
/*-- 得到指定id的父id列表 --*/
--不包含排序字段的情况
create function f_getparentid(@id int)
returns @re table(id int)
as
begin
declare @pid int
select @pid=pid from tb where id=@id
while @pid<>0
begin
insert into @re values(@pid)
select @pid=pid from tb where id=@pid
end
return
end
go
标题:SQL2005 BOM递归方法整理
(以公司组织架构为例)整理人:htl258(Tony)日期:2009.04.25(引用请保留此信息)
------------------------------------------------*/
-->创建测试环境
DECLARE @t TABLE
(
FullDept VARCHAR(20), --部门全称
Dept VARCHAR(20), --部门
ParentDept VARCHAR(20), --上级部门
Supervisor VARCHAR(20) --部门主管
)
INSERT @t SELECT 'S-IT' ,'IT','S' ,'Peter'
UNION ALL SELECT 'S-IT-CN' ,'CN','S-IT' ,'Mary'
UNION ALL SELECT 'S-IT-CN-SH' ,'SH','S-IT-CN' ,'Jack'
UNION ALL SELECT 'S-FS-AP' ,'AP','S-FS' ,'Colin'
UNION ALL SELECT 'S-FS' ,'FS','S' ,'Jerry'
UNION ALL SELECT 'S' ,'0' ,'0' ,'CiCi'
/*
SELECT * FROM @t
--------------------
FullDept(部门全称) Dept(部门) ParentDept(上级部门) Supervisor(部门主管)
-------------------- -------------------- -------------------- --------------------
S-IT IT S Peter
S-IT-CN CN S-IT Mary
S-IT-CN-SH SH S-IT-CN Jack
S-FS-AP AP S-FS Colin
S-FS FS S Jerry
S 0 0 CiCi
(6 行受影响)
*/
-->1.使用CTE递归返回指定根的子树查询:
--->例1.查询部门主管为"Peter"的所有下级部门清单
;WITH t AS
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Peter'
UNION ALL
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON a.ParentDept = b.FullDept /*原表父项等于CTE子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S-IT IT S Peter 0
S-IT-CN CN S-IT Mary 1
S-IT-CN-SH SH S-IT-CN Jack 2(3 行受影响)
*/
--->例2.查询部门主管为"Cici"的所有下级部门清单
;WITH t AS
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor ='Cici'
UNION ALL
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON a.ParentDept = b.FullDept /*原表父项等于CTE子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 0
S-FS FS S Jerry 1
S-FS-AP AP S-FS Colin 2
S-IT IT S Peter 1
S-IT-CN CN S-IT Mary 2
S-IT-CN-SH SH S-IT-CN Jack 3(6 行受影响)
*/
-->2.使用CTE递归返回指定子项的父项查询:
--->例1.查询部门主管为"Jack"的所有上级部门清单
;WITH t AS
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Jack'
UNION ALL
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept /*CTE父项等于原表子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 3
S-IT IT S Peter 2
S-IT-CN CN S-IT Mary 1
S-IT-CN-SH SH S-IT-CN Jack 0(4 行受影响)
*/
--->例2.查询部门主管为"Colin"的所有上级部门清单
;WITH t AS
(
SELECT *,0 as lvl /*展开层次*/ FROM @t WHERE Supervisor = 'Colin'
UNION ALL
SELECT a.*,b.lvl+1 FROM @t a /*原表*/ JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept /*CTE父项等于原表子项*/
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept Dept ParentDept Supervisor lvl
-------------------- -------------------- -------------------- -------------------- -----------
S 0 0 CiCi 2
S-FS FS S Jerry 1
S-FS-AP AP S-FS Colin 0(3 行受影响)
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wufeng4552/archive/2010/01/15/5193800.aspx
不好意思,忘记说了
我这是要用到asp.net(C#)网页中,上面的代码我不知要怎么整合进去。