1.建表并插入树形数据
/*
Navicat MySQL Data Transfer
Source Server : local
Source Server Version : 50540
Source Host : 127.0.0.1:3306
Source Database : bingshengone
Target Server Type : MYSQL
Target Server Version : 50540
File Encoding : 65001
Date: 2018-12-14 00:21:24
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for workorder_class
-- ----------------------------
DROP TABLE IF EXISTS `workorder_class`;
CREATE TABLE `workorder_class` (
`workOrderClassId` int(11) NOT NULL DEFAULT '0',
`parentId` int(11) NOT NULL,
`workOrderClassName` varchar(100) DEFAULT NULL,
`workOrderClassLevel` varchar(10) DEFAULT NULL,
`CustomFields1` varchar(100) DEFAULT NULL,
`CustomFields2` varchar(100) DEFAULT NULL,
`CustomFields3` varchar(100) DEFAULT NULL,
`tips` varchar(255) DEFAULT NULL,
PRIMARY KEY (`workOrderClassId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of workorder_class
-- ----------------------------
INSERT INTO `workorder_class` VALUES ('1', '0', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('2', '1', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('3', '2', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('4', '1', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('5', '1', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('6', '2', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('7', '2', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('8', '0', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('9', '0', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('10', '8', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('11', '8', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('12', '9', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('13', '12', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('14', '13', '1', '1', '1', '1', '1', '1');
INSERT INTO `workorder_class` VALUES ('15', '11', '1', '1', '1', '1', '1', '1');
ID为1的树形数据如下
2.递归查询(包括第一个数据)
select t2.workOrderClassId ,t2.parentId,t2.workOrderClassName
from workorder_class t1, workorder_class t2 where t1.workOrderClassId=t2.parentId and (t1.workOrderClassId= 1 or t1.parentId= 1 )
union select t3.workOrderClassId ,t3.workOrderClassName,t3.parentId from workorder_class t3 where t3.workOrderClassId= 1 ;
3.递归查询(不包括第一条数据)
select t2.workOrderClassId ,t2.parentId,t2.workOrderClassName
from workorder_class t1, workorder_class t2 where t1.workOrderClassId=t2.parentId and (t1.workOrderClassId= 1 or t1.parentId= 1 );