sql server 中如何实现查找下级分类以及下下级?
发布网友
发布时间：2022-04-09 21:31
共7个回答
热心网友
时间：2022-04-09 23:01
--建立测试数据
if object_id('new_tree') is not null drop table new_tree
go
/*parent对应父亲结点，child对应儿子结点，如果child is null则本结点为叶子结点*/
create table new_tree ( parent varchar(80), child varchar(80))
go
insert new_tree values ('1','2');
insert new_tree values ('1','3');
insert new_tree values ('2','4');
insert new_tree values ('2','5');
insert new_tree values ('3','6');
insert new_tree values ('3','7');
insert new_tree values ('3','8');
insert new_tree values ('6','9');
insert new_tree values ('5','10');
insert new_tree values ('4','11');
insert new_tree values ('9','12');
insert new_tree values ('7',NULL);
insert new_tree values ('8',NULL);
insert new_tree values ('10',NULL);
insert new_tree values ('11',NULL);
insert new_tree values ('12',NULL);
--创建存储过程
if object_id('proc_new_tree') is not null drop proc proc_new_tree
go
/*@parent 输入根结点标识，@mode为0 则输出为所有子孙记录，否则输出所有叶子结点*/
create proc proc_new_tree (@parent varchar(80),@mode int =0)
as
begin
set nocount on
/*如果不是SQLSERVER2000可以用临时表*/
declare @tmp1 table (parent varchar(80), child varchar(80),trace varchar(256))
declare @tmp2 table (parent varchar(80), child varchar(80),trace varchar(256))
declare @tmp3 table (parent varchar(80), child varchar(80),trace varchar(256))
insert @tmp1 select parent,child,@parent from new_tree where parent = @parent
insert @tmp3 select parent,child,@parent from new_tree where parent = @parent
/*循环的次数等于树的深度*/
while exists(select * from @tmp1 where child is not NULL)
begin
insert @tmp2 select a.parent,a.child , b.trace+'->'+a.parent
from new_tree a,@tmp1 b where a.parent = b.child
/*@tmp2表中存本次查询的层次的所有结点*/
delete from @tmp1 where child is not NULL
/*@tmp1表中最终存的是叶子结点*/
insert @tmp1 select * from @tmp2
/*@tmp3表中最保存每次查到的子孙*/
insert @tmp3 select * from @tmp2
delete from @tmp2
end
if @mode =0 select * from @tmp3
else select trace from @tmp1
set nocount off
end
go
--调用存储过程
exec proc_new_tree '6',0
exec proc_new_tree '6',1
go
--删除测试环境
drop table new_tree
drop proc proc_new_tree
/*
parent child trace
----------------------------
6 9 6
9 12 6->9
12 NULL 6->9->12
trace
-------
6->9->12
*/
------------------------------------------------------
再给一个例子：
--根据父节点生成所有子节点的记录集:
--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go
--执行查询
select ID from dbo.f_getChild(3)
go
--删除测试数据
drop function f_getChild
drop table BOM
--输出结果
/*
5
6
7
*/
热心网友
时间：2022-04-10 00:19
在文件 includes/lib_goods.php 最后加上
function get_parent_id_tree($parent_id)
{
$three_c_arr = array();
$sql = 'SELECT count(*) FROM ' . $GLOBALS['ecs']->table('category') . " WHERE parent_id = '$parent_id' AND is_show = 1 ";
if ($GLOBALS['db']->getOne($sql))
{
$child_sql = 'SELECT cat_id, cat_name, parent_id, is_show ' .
'FROM ' . $GLOBALS['ecs']->table('category') .
"WHERE parent_id = '$parent_id' AND is_show = 1 ORDER BY sort_order ASC, cat_id ASC";
$res = $GLOBALS['db']->getAll($child_sql);
foreach ($res AS $row)
{
if ($row['is_show'])
$three_c_arr[$row['cat_id']]['id'] = $row['cat_id'];
$three_c_arr[$row['cat_id']]['name'] = $row['cat_name'];
$three_c_arr[$row['cat_id']]['url'] = build_uri('category', array('cid' => $row['cat_id']), $row['cat_name']);
}
}
return $three_c_arr;
}
声明后用$smarty调用，就是在index.php中加上下面一句：
$smarty->assign('get_parent_id16_tree', get_parent_id_tree(16));//调用父级分类6的下级分类
最后就可以在index.dwt模板文件里开始调用了
<!--{foreach from=$get_parent_id16_tree item=list}-->
<a href="http://chenlihong89791781.blog.163.com/{$list.url}" target="_blank">{$list.name|truncate:15:true}</a>
| <!--{/foreach}--></div>
热心网友
时间：2022-04-10 01:53
<br>
<br>语句如下:
<br>select * from table where superior in (select sid from table where superior=19)
楼主的补冲看到了!
不地很遗憾的是SQL SERVER当中没有这种语句,建议你自己做一个存储过程!
如果要想实现的话!最笨的方法就是多套几个 in
不过像你所说的,你的级可能不止十来级吧!
自己做一个存储过程吧!
参考资料：http://www.itpub.net/199797.html
热心网友
时间：2022-04-10 03:45
热心网友
时间：2022-04-10 05:53
热心网友
时间：2022-04-10 08:17
只是你的100分悬赏有点*,我这个答案能成为最佳吗?
热心网友
时间：2022-04-10 10:59
