表:
id parentid name
1 0 test1
2 0 test2
3 1 test3
4 1 test4
5 3 test5
6 4 test6
7 5 test7
(1)请问,输入一个id,把其partentid=id的所有子节点查询出来 ,比如:输入id=3,查询得到得到下面结果:
id parentid name
5 3 test5
7 5 test7
(2)请问,输入一个id,把其partentid=id的所有非子节点的节点,并且排除本身节点查询出来,比如:输入id=3,查询得到得到下面结果:
id parentid name
1 0 test1
2 0 test2
4 1 test4
6 4 test6
请各位大哥大姐帮帮小弟,小弟急用,非常感谢了!在线等.....
------解决方案--------------------
- SQL code
例子一直就没有什么好的解决办法。DROP TABLE IF EXISTS `item_category`;CREATE TABLE `item_category` (`id` int(11) NOT NULL auto_increment,`catId` int(11) default NULL,`parentId` int(11) default NULL,PRIMARY KEY (`id`));INSERT INTO `item_category` VALUES (1,1,0);INSERT INTO `item_category` VALUES (2,2,1);INSERT INTO `item_category` VALUES (3,3,1);INSERT INTO `item_category` VALUES (4,4,2);INSERT INTO `item_category` VALUES (5,5,3);DELIMITER //drop procedure if exists iterative//CREATE PROCEDURE iterative(iid bigint(20),layer bigint(20))BEGINdeclare tid bigint(20) default -1;declare cur1 CURSOR FOR select catId from item_category where parentId=iid;declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tid = null;if layer > 0 thenOPEN cur1;FETCH cur1 INTO tid;WHILE(tid is not null)DOinsert into tmp_table values(tid);call iterative(tid,layer-1);FETCH cur1 INTO tid;END WHILE;end if;END;//DELIMITER ;call findLChild(1,2);/*2435*/
------解决方案--------------------
我用的是ms-sql2008