MYSQL级联查询，包括向上向下的级联
http://my.oschina.net/u/178116/blog/684608
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
Sql代码 -- 名称：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 |
