mysql如何實(shí)現(xiàn)遞歸查詢

DROP?TABLE?IF?EXISTS?`t_areainfo`; CREATE?TABLE?`t_areainfo`?( ?`id`?int(11)?NOT?'0'?AUTO_INCREMENT, ?`level`?int(11)?DEFAULT?'0', ?`name`?varchar(255)?DEFAULT?'0', ?`parentId`?int(11)?DEFAULT?'0', ?`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',?'華北區(qū)',?'1',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('3',?'0',?'華南區(qū)',?'1',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('4',?'0',?'北京',?'2',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('5',?'0',?'海淀區(qū)',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('6',?'0',?'豐臺區(qū)',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('7',?'0',?'朝陽區(qū)',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('8',?'0',?'北京XX區(qū)1',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('9',?'0',?'北京XX區(qū)2',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('10',?'0',?'北京XX區(qū)3',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('11',?'0',?'北京XX區(qū)4',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('12',?'0',?'北京XX區(qū)5',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('13',?'0',?'北京XX區(qū)6',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('14',?'0',?'北京XX區(qū)7',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('15',?'0',?'北京XX區(qū)8',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('16',?'0',?'北京XX區(qū)9',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('17',?'0',?'北京XX區(qū)10',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('18',?'0',?'北京XX區(qū)11',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('19',?'0',?'北京XX區(qū)12',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('20',?'0',?'北京XX區(qū)13',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('21',?'0',?'北京XX區(qū)14',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('22',?'0',?'北京XX區(qū)15',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('23',?'0',?'北京XX區(qū)16',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('24',?'0',?'北京XX區(qū)17',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('25',?'0',?'北京XX區(qū)18',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('26',?'0',?'北京XX區(qū)19',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('27',?'0',?'北京XX區(qū)1',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('28',?'0',?'北京XX區(qū)2',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('29',?'0',?'北京XX區(qū)3',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('30',?'0',?'北京XX區(qū)4',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('31',?'0',?'北京XX區(qū)5',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('32',?'0',?'北京XX區(qū)6',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('33',?'0',?'北京XX區(qū)7',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('34',?'0',?'北京XX區(qū)8',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('35',?'0',?'北京XX區(qū)9',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('36',?'0',?'北京XX區(qū)10',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('37',?'0',?'北京XX區(qū)11',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('38',?'0',?'北京XX區(qū)12',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('39',?'0',?'北京XX區(qū)13',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('40',?'0',?'北京XX區(qū)14',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('41',?'0',?'北京XX區(qū)15',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('42',?'0',?'北京XX區(qū)16',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('43',?'0',?'北京XX區(qū)17',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('44',?'0',?'北京XX區(qū)18',?'4',?'0'); INSERT?INTO?`t_areainfo`?VALUES?('45',?'0',?'北京XX區(qū)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');

DROP?FUNCTION?IF?EXISTS?queryChildrenAreaInfo; DELIMITER?;; 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?;

SELECT?queryChildrenAreaInfo(1);

SELECT?*?FROM?t_areainfo?WHERE?FIND_IN_SET(id,queryChildrenAreaInfo(4));

DROP?FUNCTION?IF?EXISTS?queryChildrenAreaInfo1; DELIMITER;; 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 ;; DELIMITER?;

SELECT?*?from?t_areainfo?where?FIND_IN_SET(id,queryChildrenAreaInfo1(7));