mysql-递归查询(二)
程序员文章站
2022-07-23 11:13:19
文章目录情景描述数据准备递归sql情景描述mysql 表查询,经常有通过子不断查询父,直至查询不到为止。数据准备SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for `city`-- ----------------------------DROP TABLE IF EXISTS `city`;CREATE TABLE `city` ( `id...
情景描述
mysql 表查询,经常有通过子不断查询父,直至查询不到为止。
数据准备
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `city`
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
`id` bigint(20) NOT NULL,
`name` varchar(40) NOT NULL,
`parentId` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `city`
-- ----------------------------
BEGIN;
INSERT INTO `city` VALUES ('1', '中国', '0'), ('2', '北京', '1'), ('3', '山西', '1'), ('4', '海淀', '2'), ('5', '昌平', '2'), ('6', '太原', '3'), ('7', '晋城', '3'), ('8', '小店', '6');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
递归sql
@r := #{id}
SELECT
t1.*
FROM
(
SELECT
@r AS _id,
(
SELECT
@r := parentId
FROM
city
WHERE
id = _id
) AS parentId,
@l := @l + 1 AS lvl
FROM
(SELECT @r := 8, @l := 0) vars,
city h
) t2
JOIN city t1 ON t1.id = t2._id
本文地址:https://blog.csdn.net/zk673820543/article/details/107630390
上一篇: 盘点苹果2016年大动向
下一篇: 多表查询中对应关系的汇总规则