Hi，这是一个很有意思的问题，二叉树，无限极分类一般都会用到递归。这里使用函数来模拟mysql查询，解决思路如下：
<?phpheader("Content-type:text/htmlcharset=utf-8")
$data = array(
array('id'=>1, 'pid'=> 0, 'name'=> 'name1'),
array('id'=>2, 'pid'=> 1, 'name'=> 'name2'),
array('id'=>3, 'pid'=> 2, 'name'=> 'name3'),
array('id'=>4, 'pid'=> 3, 'name'=> 'name4'),
array('id'=>5, 'pid'=> 2, 'name'=> 'name5'),
array('id'=>6, 'pid'=> 2, 'name'=> 'name6'),
array('id'=>7, 'pid'=> 2, 'name'=> 'name7'),
array('id'=>8, 'pid'=> 7, 'name'=> 'name8'),
array('id'=>9, 'pid'=> 8, 'name'=> 'name9'),
array('id'=>10, 'pid'=> 9, 'name'=> 'name10'),
array('id'=>11, 'pid'=> 10, 'name'=> 'name11'),
array('id'=>12, 'pid'=> 11, 'name'=> 'name12'),
array('id'=>13, 'pid'=> 12, 'name'=> 'name13'),
array('id'=>14, 'pid'=> 13, 'name'=> 'name14'),
array('id'=>15, 'pid'=> 14, 'name'=> 'name15'),
array('id'=>16, 'pid'=> 1, 'name'=> 'name16'),
array('id'=>17, 'pid'=> 16, 'name'=> 'name17'),
array('id'=>18, 'pid'=> 17, 'name'=> 'name18'),
array('id'=>19, 'pid'=> 18, 'name'=> 'name19'),
array('id'=>20, 'pid'=> 3, 'name'=> 'name20'),
array('id'=>21, 'pid'=> 3, 'name'=> 'name21'),
array('id'=>22, 'pid'=> 2, 'name'=> 'name22'),
)
$result = array()
$id = 2
$lv = 20
get_child_node_nums($id, $lv, $result)
foreach($result as $no => $row)
{
echo '第'.($lv-$no+1).'层有'.count($row).'个叶子节点'.'<br/>'
}
p($result)
//模拟mysql根据pid获取多行记录
function fetch_rows($pid=0)
{
global $data
$pid = (int)$pid
$items = array()
//相当于sql语句:select * from test where pid=$pid
echo "select * from test where pid=$pid<br/>"
foreach($data as $row)
{
if($row['pid'] == $pid)
{
$items[] = $row
}
}
return $items
}
//$id为父节点id, $lv为深度, $result为引用传值结果数组
function get_child_node_nums($id, $lv, &$result)
{
//首先根据其id作为子节点的pid获取其所有子节点
$children = fetch_rows($id)
if($children)
{
//存储其叶子节点
if(isset($result[$lv]))
{
$result[$lv] = array_merge($result[$lv], $children)
}else{
$result[$lv] = $children
}
$lv--
if($lv > 0)
{
foreach($children as $child)
{
$id = $child['id']
get_child_node_nums($id, $lv, $result)
}
}
}
}
function p($var)
{
echo '<pre>'
if($var === false)
{
echo 'false'
}else if($var === null){
print_r("null")
}else if($var === ''){
print_r("''")
}else{
print_r($var)
}
echo '</pre>'
}
输出结果如下：
select * from test where pid=2select * from test where pid=3
select * from test where pid=4
select * from test where pid=20
select * from test where pid=21
select * from test where pid=5
select * from test where pid=6
select * from test where pid=7
select * from test where pid=8
select * from test where pid=9
select * from test where pid=10
select * from test where pid=11
select * from test where pid=12
select * from test where pid=13
select * from test where pid=14
select * from test where pid=15
select * from test where pid=22
第1层有5个叶子节点
第2层有4个叶子节点
第3层有1个叶子节点
第4层有1个叶子节点
第5层有1个叶子节点
第6层有1个叶子节点
第7层有1个叶子节点
第8层有1个叶子节点
第9层有1个叶子节点
Array
(
[20] => Array
(
[0] => Array
(
[id] => 3
[pid] => 2
[name] => name3
)
[1] => Array
(
[id] => 5
[pid] => 2
[name] => name5
)
[2] => Array
(
[id] => 6
[pid] => 2
[name] => name6
)
[3] => Array
(
[id] => 7
[pid] => 2
[name] => name7
)
[4] => Array
(
[id] => 22
[pid] => 2
[name] => name22
)
)
[19] => Array
(
[0] => Array
(
[id] => 4
[pid] => 3
[name] => name4
)
[1] => Array
(
[id] => 20
[pid] => 3
[name] => name20
)
[2] => Array
(
[id] => 21
[pid] => 3
[name] => name21
)
[3] => Array
(
[id] => 8
[pid] => 7
[name] => name8
)
)
[18] => Array
(
[0] => Array
(
[id] => 9
[pid] => 8
[name] => name9
)
)
[17] => Array
(
[0] => Array
(
[id] => 10
[pid] => 9
[name] => name10
)
)
[16] => Array
(
[0] => Array
(
[id] => 11
[pid] => 10
[name] => name11
)
)
[15] => Array
(
[0] => Array
(
[id] => 12
[pid] => 11
[name] => name12
)
)
[14] => Array
(
[0] => Array
(
[id] => 13
[pid] => 12
[name] => name13
)
)
[13] => Array
(
[0] => Array
(
[id] => 14
[pid] => 13
[name] => name14
)
)
[12] => Array
(
[0] => Array
(
[id] => 15
[pid] => 14
[name] => name15
)
)
)
亲测，望采纳^_^。
1，建立测试表和数据：DROP TABLE IF EXISTS csdn.channel
CREATE TABLE csdn.channel (
id INT(11) NOT NULL AUTO_INCREMENT,
cname VARCHAR(200) DEFAULT NULL,
parent_id INT(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO channel(id,cname,parent_id)
VALUES (13,'首页',-1),
(14,'TV580',-1),
(15,'生活580',-1),
(16,'左上幻灯片',13),
(17,'帮忙',14),
(18,'栏目简介',17)
DROP TABLE IF EXISTS channel
2，利用临时表和递归过程实现树的遍历（mysql的UDF不能递归调用）：
2.1，从某节点向下遍历子节点，递归生成临时表数据
-- pro_cre_childlist
DELIMITER $$
DROP PROCEDURE IF EXISTS csdn.pro_cre_childlist$$
CREATE PROCEDURE csdn.pro_cre_childlist(IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0
DECLARE b INT
DECLARE cur1 CURSOR FOR SELECT id FROM channel WHERE parent_id=rootId
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1
SET max_sp_recursion_depth=12
INSERT INTO tmpLst VALUES (NULL,rootId,nDepth)
OPEN cur1
FETCH cur1 INTO b
WHILE done=0 DO
CALL pro_cre_childlist(b,nDepth+1)
FETCH cur1 INTO b
END WHILE
CLOSE cur1
END$$
2.2，从某节点向上追溯根节点，递归生成临时表数据
-- pro_cre_parentlist
DELIMITER $$
DROP PROCEDURE IF EXISTS csdn.pro_cre_parentlist$$
CREATE PROCEDURE csdn.pro_cre_parentlist(IN rootId INT,IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0
DECLARE b INT
DECLARE cur1 CURSOR FOR SELECT parent_id FROM channel WHERE id=rootId
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1
SET max_sp_recursion_depth=12
INSERT INTO tmpLst VALUES (NULL,rootId,nDepth)
OPEN cur1
FETCH cur1 INTO b
WHILE done=0 DO
CALL pro_cre_parentlist(b,nDepth+1)
FETCH cur1 INTO b
END WHILE
CLOSE cur1
END$$
统计所有记录的数量：SELECT COUNT(*) FROM table_name
统计某列的数量：
SELECT COUNT(column_name) FROM table_name
where 条件
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）