写一个mysql数据库的sql 递归查询,我现在有个能查询三级的,谁能帮我改为查询四级或五级的

select distinct * from
(select * from news_types where news_types.id=1 union

select n2.* from news_types n1,news_types n2 where n1.id=1 and n2.parentid=n1.id
union select n3.* from news_types n3,(
select n2.* from news_types n1,news_types n2 where n1.id=1 and n2.parentid=n1.id
) t2 where t2.id=n3.parentid) a;
写一个mysql数据库的sql 递归查询,我现在有个能查询三级的,谁能帮我改为查询四级或五级的

2个回答

又是你啊,昨天的那个不好用么,那个可是无限层的
按照你写的给你改了个5层的
[code="sql"]
select distinct *
from (select *
from news_types
where news_types.id = 1
union
select n2.*
from news_types n1, news_types n2
where n1.id = 1
and n2.parentid = n1.id
union
select n3.*
from news_types n3,
(select n2.*
from news_types n1, news_types n2
where n1.id = 1
and n2.parentid = n1.id) t2
where t2.id = n3.parentid
union
select n4.*
from news_types n4,
(select n3.*
from news_types n3,
(select n2.*
from news_types n1, news_types n2
where n1.id = 1
and n2.parentid = n1.id) t2
where t2.id = n3.parentid) t3
where t3.id = n4.parentid
union
select n5.*
from news_types n5,
(select n4.*
from news_types n4,
(select n3.*
from news_types n3,
(select n2.*
from news_types n1, news_types n2
where n1.id = 1
and n2.parentid = n1.id) t2
where t2.id = n3.parentid) t3
where t3.id = n4.parentid) t4
where t4.id = n5.parentid
) a;

[/code]

写函数吧
参考这个
[url]http://www.itpub.net/thread-972506-1-1.html[/url]
[code="sql"]
CREATE TABLE tablea (

cid int(10) unsigned NOT NULL,

pid int(10) unsigned NOT NULL,

name varchar(45) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gb2312

insert into test.tablea
(cid, pid, name)
values
(1 ,0 , '1 '),
(10 ,1 , '10 '),
(11 ,1 , '11 '),
(12 ,1 , '12 '),
(2 ,0 , '2 '),
(20 ,2 , '20 '),
(21 ,2 , '21 '),
(22 ,2 , '22 '),
(110, 10, '110'),
(120, 10, '120')

DELIMITER $$;
DROP PROCEDURE IF EXISTS p_opt_getCount$$
CREATE PROCEDURE p_opt_getCount(
in in_sql varchar(65532),
out out_count int)
BEGIN
-- get count from a select
DROP TABLE IF EXISTS xxx_t_count;
CREATE TEMPORARY TABLE xxx_t_count(num int) TYPE = HEAP;
set @sql = concat('insert xxx_t_count select count(*) from (', in_sql, ') a ');
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
select num into out_count from xxx_t_count;
-- drop temporary table for releasing memory
DROP TABLE IF EXISTS xxx_t_count;
END$$
DELIMITER ;$$
-- 递归查找
DELIMITER $$;
DROP PROCEDURE IF EXISTS p_opt_recursion$$
CREATE PROCEDURE p_opt_recursion(
in in_table_name varchar(45), -- 目标表名
in child_column_name varchar(45), -- 子字段名
in parent_column_name varchar(45), -- 父字段名
in in_start_id int, -- 从那个值的父结点开始递归
in tmp_table_name varchar(45)) -- 自定义一个表名,用来得到递归的结果
BEGIN
DECLARE v_level INT default 0;
DECLARE v_count INT default 0;
DECLARE v_sql varchar(65532) default '';
-- temporary table doesn't support self-join
DROP TABLE IF EXISTS xxx_t_recursion;
CREATE TEMPORARY TABLE xxx_t_recursion(cid int, pid int, level int, INDEX index_1(level)) TYPE = HEAP;
DROP TABLE IF EXISTS xxx_t_recursion2;
CREATE TEMPORARY TABLE xxx_t_recursion2(cid int, pid int, level int, INDEX index_1 (level)) TYPE = HEAP;
INSERT xxx_t_recursion(cid,pid,level) select in_start_id,in_start_id,v_level;
set v_sql = concat(' select a.',parent_column_name,' from ',in_table_name,' a, xxx_t_recursion b where a.',parent_column_name,' = b.cid and b.level = 0 ');
call p_opt_getCount(v_sql,v_count);
-- select v_count;
INSERT INTO xxx_t_recursion2 SELECT * FROM xxx_t_recursion;
WHILE v_count > 0 DO
SET v_level = v_level + 1;
set @sql = concat(' insert xxx_t_recursion(cid,pid,level) SELECT a.',child_column_name,',a.',parent_column_name,', ',v_level,' from ',in_table_name,' a, xxx_t_recursion2 b where a.',parent_column_name,' = b.cid and b.level = ',v_level,' - 1 ');
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
set v_sql = concat(' select a.',parent_column_name,' from ',in_table_name,' a, xxx_t_recursion b where a.',parent_column_name,' = b.cid and b.level = ',v_level);
-- get v_sql record count
call p_opt_getCount(v_sql,v_count);
INSERT INTO xxx_t_recursion2 SELECT * FROM xxx_t_recursion;
END WHILE;
delete from xxx_t_recursion where level = 0;
SELECT * FROM xxx_t_recursion;
-- create temporary table named by tmp_table_name value
-- put the result in the new_temporary table
set @sql = concat('DROP TABLE IF EXISTS ',tmp_table_name);
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
set @sql = concat('CREATE TEMPORARY TABLE ',tmp_table_name,'(cid int, pid int, level int, INDEX index_1(level)) TYPE = HEAP');
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
set @sql = concat('insert ',tmp_table_name,' select * from xxx_t_recursion ');
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
-- release memory
DROP TABLE IF EXISTS xxx_t_recursion;
DROP TABLE IF EXISTS xxx_t_recursion2;
/*set @sql = concat('select * from ',tmp_table_name);
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;*/
END$$
DELIMITER ;$$

-- 递归测试
DELIMITER $$;
DROP PROCEDURE IF EXISTS zzz_test$$
CREATE PROCEDURE zzz_test()
BEGIN
call p_opt_recursion('tablea','cid','pid',1,'xxx_table');
select * from xxx_table;
END$$
DELIMITER ;$$


call zzz_test();
[/code]

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐