INSERTINTO department (dep_id, dep_name, parent_id, dep_path, del_flag, parent_flag, enabled)VALUES(1,'a0',null,'1',0,1,1),(2,'b0',1,'1,2',0,1,1),(3,'b1',1,'1,3',0,1,1),(4,'b2',1,'1,4',0,1,1),(5,'c0',2,'1,2,5',0,1,1),(6,'c1',2,'1,2,6',0,1,1),(7,'c2',2,'1,2,7',0,1,1),(8,'c3',2,'1,2,8',0,0,1),(9,'c4',2,'1,2,9',0,1,1),(10,'c5',3,'1,3,10',0,1,1),(11,'c6',3,'1,3,11',0,0,1),(12,'c7',3,'1,3,12',0,0,1),(13,'c8',4,'1,4,13',0,0,1),(14,'d0',5,'1,2,5,14',0,0,1),(15,'d1',5,'1,2,5,15',0,0,1),(16,'d2',5,'1,2,5,16',0,0,1),(17,'d3',5,'1,2,5,17',0,0,1),(18,'d4',12,'1,3,12,18',0,0,1),(19,'d5',12,'1,3,12,19',0,0,1),(20,'d6',12,'1,3,12,20',0,0,1),(21,'d7',5,'1,2,5,21',0,0,1),(22,'d8',13,'1,4,13,22',0,0,1),(23,'d9',9,'1,2,9,23',0,0,1),(24,'d10',9,'1,2,9,24',0,0,1),(25,'d11',7,'1,2,7,25',0,0,1),(26,'d12',10,'1,3,10,26',0,0,1);
查询
SELECT B.dep_id, B.dep_name, B.dep_path, A.LEVELFROM(SELECT@idsAS _ids,(SELECT@ids := GROUP_CONCAT(dep_id)FROM department
WHERE FIND_IN_SET(parent_id,@ids))AS cids,@l :=@l+1ASLEVELFROM department,(SELECT@ids :=5,@l :=0) C
WHERE@idsISNOTNULL) A,
department B
WHERE FIND_IN_SET(B.dep_id, A._ids);