欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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;

mysql-递归查询(二)

递归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

mysql-递归查询(二)

本文地址:https://blog.csdn.net/zk673820543/article/details/107630390

相关标签: mysql