mysql读取树形结构所有子节点 mysql递归查询 详解 存储过程详解 查询所有子节点详解
call Pro_GetUnderOrg(‘a2a8d12675284d5cb38c879e542aac9f’);
create procedure Pro_GetAllChildrenTypes(in pid varchar(36)) –创建存储过程Pro_GetAllChildrenTypes 输入参数pid
begin
declare lev int;–定义变量lev层次
set lev=1;–层次赋值1
drop table if exists tmp1;
CREATE TABLE tmp1(id VARCHAR(40),typename varchar(50),parentid varchar(40) ,levv INT); –创建临时表
INSERT tmp1 SELECT id,typename,parentid,1 FROM `ulib_resourcetype` WHERE parentid=pid; –查询第一级子节点并插入到临时表tmp1中
while row_count()>0 –ROW_COUNT()表示数据库中数据更新的条数，这里如果上面(或者下面)给tmp1中插入了数据则表示有子节点则继续执行即每个层次都查询出了下级子类
do set lev=lev+1; –层次加1
INSERT tmp1 SELECT t.id,t.typename,t.parentid,lev from ulib_resourcetype t join tmp1 a on t.parentid=a.id AND levv=lev-1;–查询出下一层次所有子节点
end while ;
INSERT tmp1 SELECT id,typename,parentid,0 FROM ulib_resourcetype WHERE id=idd; –查询出当前节点
SELECT * FROM tmp1;–查询出所有tmp1
end;
————————————————
— —————————-
— Table structure for `ulib_resourcetype`
— —————————-
DROP TABLE IF EXISTS `ulib_resourcetype`;
CREATE TABLE `ulib_resourcetype` (
`id` varchar(40) NOT NULL,
`typename` varchar(200) default NULL,
`ordernum` bigint(20) default NULL,
`parentid` varchar(40) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
———————————————
— —————————-
— Records of ulib_resourcetype
— —————————-
INSERT INTO ulib_resourcetype VALUES (’03f17420f0e74df0b51f75d2c437a674′, ‘世界文化遗产’, ‘1348219982224’, ‘root’, ‘1’, ‘2012-09-21’);
INSERT INTO ulib_resourcetype VALUES (‘0b23e45c281b4181aef0b5ba674779a3’, ‘省情概况’, ‘1348219971311’, ‘root’, ‘1’, ‘2012-09-21’);
INSERT INTO ulib_resourcetype VALUES (‘14688796163449029944679d946276d2’, ‘天津市’, ‘1348564088276’, ‘0b23e45c281b4181aef0b5ba674779a3’, ‘0’, ‘2012-09-25’);
INSERT INTO ulib_resourcetype VALUES (‘329b93c82ecf4641b6144ce20af4e388′, ’11’, ‘1349765942928’, ‘6653ea0bb59d496e90f031cf4f155f21’, ‘0’, ‘2012-10-09’);
INSERT INTO ulib_resourcetype VALUES (‘3a8c04f2a19b4ab2b31310d2f7dc2b44’, ‘ds22’, ‘1348817870405’, ‘7749’, ‘0’, ‘2012-09-28’);
INSERT INTO ulib_resourcetype VALUES (‘3ec5cef7ebb245f08248502e09be00b7’, ‘122’, ‘1349659424969’, ‘b68ed09450b84a8e9c877cb54d663124’, ‘0’, ‘2012-10-08’);
INSERT INTO ulib_resourcetype VALUES (‘3f51748aa20d4f57bb6745ce74e4ba2d’, ‘ss’, ‘1348817979745’, ‘7749’, ‘1’, ‘2012-09-28’);
INSERT INTO ulib_resourcetype VALUES (‘42241d5f6a8244038e775aa1f9035c49’, ‘ces1’, ‘1349765809553’, ‘7744’, ‘1’, ‘2012-10-09’);
INSERT INTO ulib_resourcetype VALUES (‘438d03f105a54aa0a20b9e046609dbc1’, ‘ddddd’, ‘1348543797951’, ‘277bc9c2bd6241d19a21f081fc038351’, ‘0’, ‘2012-09-25’);
INSERT INTO ulib_resourcetype VALUES (‘4eed4c892c3b4b5a9425fcbfa58f1e6a’, ‘测试标准’, ‘1349768427358’, ‘root’, ‘0’, ‘2012-10-09’);
INSERT INTO ulib_resourcetype VALUES (‘542b1fcc6cca4da9b7080bfef100dd50’, ‘fffff’, ‘1348543813581’, ‘277bc9c2bd6241d19a21f081fc038351’, ‘0’, ‘2012-09-25’);
INSERT INTO ulib_resourcetype VALUES (‘5592ab401c8c4d9fb3c7832f2c5be832’, ‘2324’, ‘1349659430136’, ‘b68ed09450b84a8e9c877cb54d663124’, ‘0’, ‘2012-10-08’);
INSERT INTO ulib_resourcetype VALUES (‘65771b8e94d6461694786e15e190ee0a’, ‘北京市’, ‘1348564096548’, ‘0b23e45c281b4181aef0b5ba674779a3’, ‘0’, ‘2012-09-25’);
INSERT INTO ulib_resourcetype VALUES (‘6653ea0bb59d496e90f031cf4f155f21’, ‘1’, ‘1349765916216’, ‘7745’, ‘1’, ‘2
