mysql中级联查询_MYSQL级联查询，包括向上向下的级联

http://my.oschina.net/u/178116/blog/684608

-- 名称：mysql递归查询存储过程(2014-04-05)

-- 入:@table 表名[varchar(200)]

-- 入:@field 要查询返回的字段名(例如: name,age,remark)[text]

-- 入:@order 返回结果的排序(例如 name desc,age asc)[text]

-- 入:@idName 主键列名[varchar(200)]

-- 入:@pidName父键列名[varchar(200)]

-- 入:@id 主键值[varchar(200)],不能为null,如果值是null,说明要查询全部,请自己查询

-- 入:@upDown 向上级联还是向下级联,1=上 0=下[int]

-- 入:@self 是否包含自己 1=是 0=否[int]

-- 返回查询结果集

DROP PROCEDURE IF EXISTS Query_Dg;

CREATE PROCEDURE Query_Dg

(

IN table_ VARCHAR(200),

IN field_ TEXT,

IN order_ TEXT,

IN idName_ VARCHAR(200),

IN pidName_ VARCHAR(200),

IN id_ VARCHAR(200),

IN upDown_ INT,

IN self_ INT

)

BEGIN

DECLARE _sqlStr VARCHAR(4000); -- 动态sql

DECLARE _idParam VARCHAR(4000); -- id存放的变量

DECLARE _idSet VARCHAR(4000); -- 结果

SET @_idSet = '';

-- 查询id开始

IF(self_ = 1)THEN -- 包含自己

SET @_idSet = id_;

END IF;

-- 递归开始

IF(upDown_ = 1)THEN -- 向上递归

SET @_sql = CONCAT('SELECT ',pidName_,' INTO @_idParam FROM ',table_,' WHERE ',idName_,' = ?');

ELSE -- 向下递归

SET @_sql = CONCAT('SELECT GROUP_CONCAT(',idName_,') INTO @_idParam FROM ',table_,' WHERE FIND_IN_SET(',pidName_,', ?) > 0');

END IF;

SET @_idParam = id_; -- 输入参数使用时不能 @

PREPARE _sqlStr FROM @_sql;

EXECUTE _sqlStr USING @_idParam;

WHILE @_idParam IS NOT NULL DO

SET @_idSet = CONCAT(@_idSet,',',@_idParam);

EXECUTE _sqlStr USING @_idParam;

END WHILE;

-- 查询id结束

SET @_sql = CONCAT('SELECT ',field_,' FROM ',table_,' WHERE FIND_IN_SET(',idName_,', ? ) > 0 order by ',order_); -- 查询

DEALLOCATE PREPARE _sqlStr; -- 解除预编译

PREPARE _sqlStr FROM @_sql; -- 重新预编译

EXECUTE _sqlStr USING @_idSet;

DEALLOCATE PREPARE _sqlStr;

END

分享到：

2016-05-31 11:47

浏览 1432

分类:数据库

评论