Mybatis中实现递归查询
创建数据
DROP TABLE IF EXISTS `sys_depart`;
CREATE TABLE `sys_depart` (
`id` varchar(32) NOT NULL COMMENT 'ID',
`parent_id` varchar(32) DEFAULT NULL COMMENT '父机构ID',
`depart_name` varchar(100) NOT NULL COMMENT '机构/部门名称',
`depart_name_en` varchar(500) DEFAULT NULL COMMENT '英文名',
`depart_name_abbr` varchar(500) DEFAULT NULL COMMENT '缩写',
`depart_order` int(11) DEFAULT '0' COMMENT '排序',
`description` varchar(500) DEFAULT NULL COMMENT '描述',
`org_category` varchar(10) NOT NULL DEFAULT '1' COMMENT '机构类别 1组织机构，2岗位',
`org_type` varchar(10) DEFAULT NULL COMMENT '机构类型 1一级部门 2子部门',
`org_code` varchar(64) NOT NULL COMMENT '机构编码',
`mobile` varchar(32) DEFAULT NULL COMMENT '手机号',
`fax` varchar(32) DEFAULT NULL COMMENT '传真',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`memo` varchar(500) DEFAULT NULL COMMENT '备注',
`status` varchar(1) DEFAULT NULL COMMENT '状态（1启用，0不启用）',
`del_flag` varchar(1) DEFAULT NULL COMMENT '删除状态（0，正常，1已删除）',
`create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建日期',
`update_by` varchar(32) DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新日期',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uniq_depart_org_code` (`org_code`) USING BTREE,
KEY `index_depart_parent_id` (`parent_id`) USING BTREE,
KEY `index_depart_depart_order` (`depart_order`) USING BTREE,
KEY `index_depart_org_code` (`org_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='组织机构表';
-- ----------------------------
-- Records of sys_depart
-- ----------------------------
INSERT INTO `sys_depart` VALUES ('2c9533f930b346519026a6f7c5f94fe8', '9b88ba1475ef448dbed32a6ad95d9989', '四级市场', null, null, '0', null, '2', '5', 'A01A03A01A01A01', null, null, null, null, null, '0', 'admin', '2020-10-20 16:28:07', null, null);
INSERT INTO `sys_depart` VALUES ('4f1765520d6346f9bd9c79e2479e5b12', 'c6d7cb4deeac411cb3384b1b31278596', '市场部', null, null, '0', null, '1', '2', 'A01A03', null, null, null, null, null, '0', 'admin', '2019-02-20 17:15:34', 'admin', '2019-02-26 16:36:18');
INSERT INTO `sys_depart` VALUES ('5159cde220114246b045e574adceafe9', '6d35e179cd814e3299bd588ea7daed3f', '研发部', null, null, '0', null, '1', '2', 'A02A02', null, null, null, null, null, '0', 'admin', '2019-02-26 16:44:38', 'admin', '2019-03-07 09:36:53');
INSERT INTO `sys_depart` VALUES ('57197590443c44f083d42ae24ef26a2c', 'c6d7cb4deeac411cb3384b1b31278596', '研发部', null, null, '0', null, '1', '2', 'A01A05', null, null, null, null, null, '0', 'admin', '2019-02-21 16:14:41', 'admin', '2019-03-27 19:05:49');
INSERT INTO `sys_depart` VALUES ('63775228b7b041a99825f79760590b7d', '57197590443c44f083d42ae24ef26a2c', '研发经理', null, null, '0', null, '3', '3', 'A01A05A01', null, null, null, null, null, '0', 'admin', '2020-05-02 15:29:09', null, null);
INSERT INTO `sys_depart` VALUES ('648bd701e1f64984acd24222e725b8b6', '4f1765520d6346f9bd9c79e2479e5b12', '市场部', null, null, '0', null, '2', '3', 'A01A03A01', null, null, null, null, null, '0', 'admin', '2020-10-20 13:40:14', null, null);
INSERT INTO `sys_depart` VALUES ('6d35e179cd814e3299bd588ea7daed3f', '', '北京互动', null, null, '0', null, '1', '1', 'A02', null, null, null, null, null, '0', 'admin', '2019-02-26 16:36:39', 'admin', '2020-05-02 18:21:22');
INSERT INTO `sys_depart` VALUES ('743ba9dbdc114af8953a11022ef3096a', 'f28c6f53abd841ac87ead43afc483433', '财务部', null, null, '0', null, '1', '2', 'A03A01', null, null, null, null, null, '0', 'admin', '2019-03-22 16:45:43', null, null);
INSERT INTO `sys_depart` VALUES ('9b88ba1475ef448dbed32a6ad95d9989', '648bd701e1f64984acd24222e725b8b6', '三级市场', null, null, '0', null, '2', '4', 'A01A03A01A01', null, null, null, null, null, '0', 'admin', '2020-10-20 16:27:58', null, null);
INSERT INTO `sys_depart` VALUES ('a7d7e77e06c84325a40932163adcdaa6', '6d35e179cd814e3299bd588ea7daed3f', '财务部', null, null, '0', null, '1', '2', 'A02A01', null, null, null, null, null, '0', 'admin', '2019-02-26 16:36:47', 'admin', '2019-02-26 16:37:25');
INSERT INTO `sys_depart` VALUES ('c6d7cb4deeac411cb3384b1b31278596', '', '北京软件', null, null, '0', null, '1', '1', 'A01', null, null, null, null, null, '0', 'admin', '2019-02-11 14:21:51', 'admin', '2020-05-02 18:21:27');
entity
/**
* <p>
* 部门表
* <p>
*
* @Author Steve
* @Since 2019-01-22
*/
@Data
public class SysDepartT implements Serializable {
private static final long serialVersionUID = 1L;
/**ID*/
@TableId(type = IdType.ASSIGN_ID)
private String id;
/**父机构ID*/
private String parentId;
/**机构/部门名称*/
@Excel(name="机构/部门名称",width=15)
private String departName;
/**英文名*/
@Excel(name="英文名",width=15)
private String departNameEn;
/**缩写*/
private String departNameAbbr;
/**排序*/
@Excel(name="排序",width=15)
private Integer departOrder;
/**描述*/
@Excel(name="描述",width=15)
private String description;
/**机构类别 1组织机构，2岗位*/
@Excel(name="机构类别",width=15,dicCode="org_category")
private String orgCategory;
/**机构类型*/
private String orgType;
/**机构编码*/
@Excel(name="机构编码",width=15)
private String orgCode;
/**手机号*/
@Excel(name="手机号",width=15)
private String mobile;
/**传真*/
@Excel(name="传真",width=15)
private String fax;
/**地址*/
@Excel(name="地址",width=15)
private String address;
/**备注*/
@Excel(name="备注",width=15)
private String memo;
/**状态（1启用，0不启用）*/
@Dict(dicCode = "depart_status")
private String status;
/**删除状态（0，正常，1已删除）*/
@Dict(dicCode = "del_flag")
private String delFlag;
/**创建人*/
private String createBy;
/**创建日期*/
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
private Date createTime;
/**更新人*/
private String updateBy;
/**更新日期*/
@JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
@DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
private Date updateTime;
private List<SysDepartT> sysDeparts = new ArrayList<>();
/**
* 重写equals方法
*/
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}
if (!super.equals(o)) {
return false;
}
SysDepartT depart = (SysDepartT) o;
return Objects.equals(id, depart.id) &&
Objects.equals(parentId, depart.parentId) &&
Objects.equals(departName, depart.departName) &&
Objects.equals(departNameEn, depart.departNameEn) &&
Objects.equals(departNameAbbr, depart.departNameAbbr) &&
Objects.equals(departOrder, depart.departOrder) &&
Objects.equals(description, depart.description) &&
Objects.equals(orgCategory, depart.orgCategory) &&
Objects.equals(orgType, depart.orgType) &&
Objects.equals(orgCode, depart.orgCode) &&
Objects.equals(mobile, depart.mobile) &&
Objects.equals(fax, depart.fax) &&
Objects.equals(address, depart.address) &&
Objects.equals(memo, depart.memo) &&
Objects.equals(status, depart.status) &&
Objects.equals(delFlag, depart.delFlag) &&
Objects.equals(createBy, depart.createBy) &&
Objects.equals(createTime, depart.createTime) &&
Objects.equals(updateBy, depart.updateBy) &&
Objects.equals(updateTime, depart.updateTime);
}
/**
* 重写hashCode方法
*/
@Override
public int hashCode() {
return Objects.hash(super.hashCode(), id, parentId, departName,
departNameEn, departNameAbbr, departOrder, description,orgCategory,
orgType, orgCode, mobile, fax, address, memo, status,
delFlag, createBy, createTime, updateBy, updateTime);
}
}
Controller
/**
* 通过id查询
* @param id
* @return
*/
@AutoLog(value = "递归一")
@ApiOperation(value="递归一", notes="递归")
@GetMapping(value = "/getDepartList")
public ResponseEntityT<List<SysDepartT>> getDepartList(@RequestParam(name="id",required=true) String id) {
return ResponseEntityT.OK(sysDepartService.getDepartList(id));
}
service
List<SysDepartT> getDepartList(String id);
ServiceImpl
@Override
public List<SysDepartT> getDepartList(String id) {
return baseMapper.getDepartList(id);
}
Mapper
List<SysDepartT> getDepartList(@Param("departId") String departId);
xml
<resultMap id="getSelf" type="org.jeecg.modules.system.entity.SysDepartT">
<id column="id" property="id"></id>
<collection property="sysDeparts" select="getDepartList" column="id"></collection>
</resultMap>
<select id="getDepartList" resultMap="getSelf">
select * from sys_depart where del_flag = '0' AND parent_id=#{departId}
</select>
xml 传递多值
<resultMap id="getSelf" type="org.jeecg.modules.ai.DTO.AiColumnList">
<result column="id" property="id"/>
<result column="column_type" property="columnType"/>
<result column="pid" property="pid"/>
<collection property="aiColumnLists" select="getColumnList" column="{pid=id,column_type=column_type}">
<result column="pid" property="pid"/>
<result column="column_type" property="columnType"/>
</collection>
</resultMap>
<select id="getColumnList" resultMap="getSelf" parameterType="java.util.Map" >
select id 'key',column_name title,ai_column.* from ai_column where status=1 AND pid=#{pid}
<if test="column_type!=null and column_type!=''">
AND column_type=#{column_type}
</if>
ORDER BY column_type,sorting,create_time
</select>
@Data
public class AiColumnList {
/**主键*/
@ApiModelProperty(value = "主键")
private String key;
@ApiModelProperty(value = "栏目名称")
private String title;
/**主键*/
@ApiModelProperty(value = "主键")
private String id;
/**栏目类型*/
@ApiModelProperty(value = "栏目类型")
private String columnType;
/**上级ID（顶级0）*/
@ApiModelProperty(value = "上级ID（顶级0）")
private String pid;
/**外链地址*/
@ApiModelProperty(value = "外链地址")
private String outsideChainUrl;
/**图标[压缩后]*/
@ApiModelProperty(value = "图标[压缩后]")
private String iocReduce;
/**排序*/
@Excel(name = "排序", width = 15)
@ApiModelProperty(value = "排序")
private Integer sorting;
private List<AiColumnList> aiColumnLists;
}
执行结果
{
"result": [{
"id": "4f1765520d6346f9bd9c79e2479e5b12",
"parentId": "c6d7cb4deeac411cb3384b1b31278596",
"departName": "市场部",
"departNameEn": null,
"departNameAbbr": null,
"departOrder": 0,
"description": null,
"orgCategory": "1",
"orgType": "2",
"orgCode": "A01A03",
"mobile": null,
"fax": null,
"address": null,
"memo": null,
"status": null,
"delFlag": "0",
"createBy": "admin",
"createTime": "2019-02-20 17:15:34",
"updateBy": "admin",
"updateTime": "2019-02-26 16:36:18",
"sysDeparts": [{
"id": "648bd701e1f64984acd24222e725b8b6",
"parentId": "4f1765520d6346f9bd9c79e2479e5b12",
"departName": "市场部",
"departNameEn": null,
"departNameAbbr": null,
"departOrder": 0,
"description": null,
"orgCategory": "2",
"orgType": "3",
"orgCode": "A01A03A01",
"mobile": null,
"fax": null,
"address": null,
"memo": null,
"status": null,
"delFlag": "0",
"createBy": "admin",
"createTime": "2020-10-20 13:40:14",
"updateBy": null,
"updateTime": null,
"sysDeparts": [{
"id": "9b88ba1475ef448dbed32a6ad95d9989",
"parentId": "648bd701e1f64984acd24222e725b8b6",
"departName": "三级市场",
"departNameEn": null,
"departNameAbbr": null,
"departOrder": 0,
"description": null,
"orgCategory": "2",
"orgType": "4",
"orgCode": "A01A03A01A01",
"mobile": null,
"fax": null,
"address": null,
"memo": null,
"status": null,
"delFlag": "0",
"createBy": "admin",
"createTime": "2020-10-20 16:27:58",
"updateBy": null,
"updateTime": null,
"sysDeparts": [{
"id": "2c9533f930b346519026a6f7c5f94fe8",
"parentId": "9b88ba1475ef448dbed32a6ad95d9989",
"departName": "四级市场",
"departNameEn": null,
"departNameAbbr": null,
"departOrder": 0,
"description": null,
"orgCategory": "2",
"orgType": "5",
"orgCode": "A01A03A01A01A01",
"mobile": null,
"fax": null,
"address": null,
"memo": null,
"status": null,
"delFlag": "0",
"createBy": "admin",
"createTime": "2020-10-20 16:28:07",
"updateBy": null,
"updateTime": null,
"sysDeparts": []
}]
}]
}]
}, {
"id": "57197590443c44f083d42ae24ef26a2c",
"parentId": "c6d7cb4deeac411cb3384b1b31278596",
"departName": "研发部",
"departNameEn": null,
"departNameAbbr": null,
"departOrder": 0,
"description": null,
"orgCategory": "1",
"orgType": "2",
"orgCode": "A01A05",
"mobile": null,
"fax": null,
"address": null,
"memo": null,
"status": null,
"delFlag": "0",
"createBy": "admin",
"createTime": "2019-02-21 16:14:41",
"updateBy": "admin",
"updateTime": "2019-03-27 19:05:49",
"sysDeparts": [{
"id": "63775228b7b041a99825f79760590b7d",
"parentId": "57197590443c44f083d42ae24ef26a2c",
"departName": "研发经理",
"departNameEn": null,
"departNameAbbr": null,
"departOrder": 0,
"description": null,
"orgCategory": "3",
"orgType": "3",
"orgCode": "A01A05A01",
"mobile": null,
"fax": null,
"address": null,
"memo": null,
"status": null,
"delFlag": "0",
"createBy": "admin",
"createTime": "2020-05-02 15:29:09",
"updateBy": null,
"updateTime": null,
"sysDeparts": []
}]
}],
"message": "请求成功",
"code": 200,
"success": true
}
MySQL 直接查询
SQL
select * from sys_depart where del_flag = '0' and org_code like concat((select org_code from sys_depart where id='c6d7cb4deeac411cb3384b1b31278596'),'%') ORDER BY org_code
显示结果
MySQL函数实现递归查询
出错信息：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
原因：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
解决方法：
SQL code
show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
set global log_bin_trust_function_creators=1;
show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON |
+---------------------------------+-------+
这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.cnf配置文件中添加：
log_bin_trust_function_creators=1
创建数据
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_areainfo
-- ----------------------------
DROP TABLE IF EXISTS `t_areainfo`;
CREATE TABLE `t_areainfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`level` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`parentId` int(11) DEFAULT NULL,
`status` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of t_areainfo
-- ----------------------------
INSERT INTO `t_areainfo` VALUES ('1', '0', '中国', '0', '0');
INSERT INTO `t_areainfo` VALUES ('2', '0', '华北区', '1', '0');
INSERT INTO `t_areainfo` VALUES ('3', '0', '华南区', '1', '0');
INSERT INTO `t_areainfo` VALUES ('4', '0', '北京', '2', '0');
INSERT INTO `t_areainfo` VALUES ('5', '0', '海淀区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('6', '0', '丰台区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('7', '0', '朝阳区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('8', '0', '北京XX区1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('9', '0', '北京XX区2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('10', '0', '北京XX区3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('11', '0', '北京XX区4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('12', '0', '北京XX区5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('13', '0', '北京XX区6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('14', '0', '北京XX区7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('15', '0', '北京XX区8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('16', '0', '北京XX区9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('17', '0', '北京XX区10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('18', '0', '北京XX区11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('19', '0', '北京XX区12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('20', '0', '北京XX区13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('21', '0', '北京XX区14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('22', '0', '北京XX区15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('23', '0', '北京XX区16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('24', '0', '北京XX区17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('25', '0', '北京XX区18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('26', '0', '北京XX区19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('27', '0', '北京XX区1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('28', '0', '北京XX区2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('29', '0', '北京XX区3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('30', '0', '北京XX区4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('31', '0', '北京XX区5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('32', '0', '北京XX区6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('33', '0', '北京XX区7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('34', '0', '北京XX区8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('35', '0', '北京XX区9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('36', '0', '北京XX区10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('37', '0', '北京XX区11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('38', '0', '北京XX区12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('39', '0', '北京XX区13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('40', '0', '北京XX区14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('41', '0', '北京XX区15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('42', '0', '北京XX区16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('43', '0', '北京XX区17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('44', '0', '北京XX区18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('45', '0', '北京XX区19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('46', '0', 'xx省1', '1', '0');
INSERT INTO `t_areainfo` VALUES ('47', '0', 'xx省2', '1', '0');
INSERT INTO `t_areainfo` VALUES ('48', '0', 'xx省3', '1', '0');
INSERT INTO `t_areainfo` VALUES ('49', '0', 'xx省4', '1', '0');
INSERT INTO `t_areainfo` VALUES ('50', '0', 'xx省5', '1', '0');
INSERT INTO `t_areainfo` VALUES ('51', '0', 'xx省6', '1', '0');
INSERT INTO `t_areainfo` VALUES ('52', '0', 'xx省7', '1', '0');
INSERT INTO `t_areainfo` VALUES ('53', '0', 'xx省8', '1', '0');
INSERT INTO `t_areainfo` VALUES ('54', '0', 'xx省9', '1', '0');
INSERT INTO `t_areainfo` VALUES ('55', '0', 'xx省10', '1', '0');
INSERT INTO `t_areainfo` VALUES ('56', '0', 'xx省11', '1', '0');
INSERT INTO `t_areainfo` VALUES ('57', '0', 'xx省12', '1', '0');
INSERT INTO `t_areainfo` VALUES ('58', '0', 'xx省13', '1', '0');
INSERT INTO `t_areainfo` VALUES ('59', '0', 'xx省14', '1', '0');
INSERT INTO `t_areainfo` VALUES ('60', '0', 'xx省15', '1', '0');
INSERT INTO `t_areainfo` VALUES ('61', '0', 'xx省16', '1', '0');
INSERT INTO `t_areainfo` VALUES ('62', '0', 'xx省17', '1', '0');
INSERT INTO `t_areainfo` VALUES ('63', '0', 'xx省18', '1', '0');
INSERT INTO `t_areainfo` VALUES ('64', '0', 'xx省19', '1', '0');
向下递归
利用find_in_set()函数和group_concat()函数实现递归查询：
DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
RETURN sTemp;
END;
调用方式
SELECT queryChildrenAreaInfo(1);
查询id为"4"下面的所有节点
SELECT * FROM t_areainfo WHERE FIND_IN_SET(id,queryChildrenAreaInfo(4));
向上递归
DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;
CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
END WHILE;
RETURN sTemp;
END;
调用方式
查询id为"7"的节点的所有上级节点
SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));
