DROP TABLE IF EXISTS `sys_organization`;
CREATE TABLE `sys_organization` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`address` varchar(100) DEFAULT NULL,
`code` varchar(64) NOT NULL,
`icon` varchar(32) DEFAULT NULL,
`pid` int(11) DEFAULT NULL,
`seq` tinyint(1) NOT NULL DEFAULT '0',
`createdatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `FK_7fssu67fw54bf6fbo1iwr756b` (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='组织机构';
INSERT INTO `sys_organization` VALUES (1, '牛逼集团', '地址', '01', 'icon_company', NULL, 0, '2014-2-19 01:00:00');
INSERT INTO `sys_organization` VALUES (2, '牛逼子公司', '', '02', 'icon_folder', 1, -1, '2018-11-5 10:07:04');
INSERT INTO `sys_organization` VALUES (3, '牛逼二子公司', '', '03', 'icon_folder', 1, 0, '2018-11-5 15:12:36');
INSERT INTO `sys_organization` VALUES (4, '财务部', '', '04', 'icon_folder', 2, 0, '2018-11-5 15:13:09');
INSERT INTO `sys_organization` VALUES (5, '技术部', '', '06', 'icon_folder', 2, 0, '2018-11-6 08:47:36');
INSERT INTO `sys_organization` VALUES (6, '科学部', '', '07', 'icon_folder', 2, 0, '2018-11-6 08:48:53');
INSERT INTO `sys_organization` VALUES (7, '科技部', '', '08', 'icon_folder', 2, 0, '2018-11-6 08:49:55');
INSERT INTO `sys_organization` VALUES (10, 'å¯æ°¸å', '', '111', 'icon_folder', 2, 0, '2018-11-7 09:47:16');
SQL语句
select id from (
select t1.id,
if(find_in_set(pid, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
from (
select id,pid from sys_organization t where 1 = 1 order by pid, id
) t1,
(select @pids := 1) t2
) t3 where ischild != 0
结果集
ORClE 递归向下查询
SELECT
*
FROM
kms_Knowledge_Category START WITH FD_PARENT_ID IN (
'169480b24659f60f8617b0a4b12b2249'
) CONNECT BY PRIOR FD_ID = FD_PARENT_ID