--创建数据表
DROP
TABLE
IF EXISTS `t_areainfo`;
CREATE
TABLE
`t_areainfo` (
`id`
int
(11)
NOT
NULL
AUTO_INCREMENT,
`
level
`
int
(11)
DEFAULT
0,
`
name
`
varchar
(255),
`parentId`
int
(11),
`status`
int
(11)
DEFAULT
0,
PRIMARY
KEY
(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65
DEFAULT
CHARSET=utf8;
--给表插入数据
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'
);
--方式一:采用function获取所有子节点的id
--查询传入areaId及其以下所有子节点
delimiter //
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
//
delimiter //
--调用
function函数
方式
select
queryChildrenAreaInfo(1);
select
*
from
t_areainfo
where
FIND_IN_SET(id, queryChildrenAreaInfo(20));