基于【 MySql 】三 || 复杂sql语句
1、自关联表查询所有上级和下级信息
1.表结构如下所示（脚本数据请加QQ群免费获取）:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `hy_area`;
CREATE TABLE `hy_area` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`pid` int(0) NULL DEFAULT NULL COMMENT '父id',
`short_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '简称',
`name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名称',
`merger_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '全称',
`level` tinyint(0) NULL DEFAULT NULL COMMENT '层级 0 1 2 省市区县',
`pinyin` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '拼音',
`code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '长途区号',
`zip_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮编',
`first` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '首字母',
`lng` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '经度',
`lat` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '纬度',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3750 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
SET FOREIGN_KEY_CHECKS = 1;
2.查询所有上级
-- 查询所有上级
SELECT
T2.id,
T2.pid,
T2.short_name,
T2.`name`,
T2.merger_name,
T2.`level`,
T2.pinyin,
T2.`code`,
T2.zip_code,
T2.`first`,
T2.lng,
T2.lat,
T2.fid
FROM
(
SELECT
@r AS _id,
( SELECT @r := pid FROM hy_area WHERE id = _id ) AS parent,
@l := @l + 1 AS lvl
FROM
( SELECT @r := 2682, @l := 3 ) vars,
hy_area h
WHERE
@r <> 0
) T1
JOIN hy_area T2 ON T1._id = T2.id
ORDER BY
T2.id;
3.查询所有下级
-- 查询所有下级
SELECT
T2.id,
T2.pid,
T2.short_name,
T2.`name`,
T2.merger_name,
T2.`level`,
T2.pinyin,
T2.`code`,
T2.zip_code,
T2.`first`,
T2.lng,
T2.lat,
T2.fid
FROM
(
SELECT
@ids AS _ids,
( SELECT @ids := GROUP_CONCAT( id ) FROM hy_area WHERE FIND_IN_SET( pid, @ids ) ) AS cids,
@l := @l + 1 AS lvl
FROM
hy_area,
( SELECT @ids := 2671, @l := 2 ) b
WHERE
@ids IS NOT NULL
) id,
hy_area T2
WHERE
FIND_IN_SET( T2.id, id._ids )
ORDER BY
lvl,
id;
2、学习交流QQ群【883210148】
3、关注微信公众号获取更多资源
