Oracle转换Mysql之start with:
oracle转mysql其实很多细节,这边就不一 一 描述了,这边先整理些“坎”吧!
Oracle中start with … :
select * from
grade_group gg
start with gg.group_code=#{groupCode}
connect by gg.parent_id= prior gg.id
order by gg.group_level
转换为MySql为:
SELECT
*
FROM
grade_group
WHERE FIND_IN_SET(ID, getGradeGroupChild(#{groupCode}))
ORDER BY group_level
这里并非只要简单改下sql就行,可以看出需要一个函数支撑"getGradeGroupChild",下面提供了这个函数:
DROP FUNCTION IF EXISTS `getGradeGroupChild`;
DELIMITER $$
CREATE FUNCTION `getGradeGroupChild`(GROUPCODE VARCHAR(100)) RETURNS VARCHAR(10000) CHARSET utf8
BEGIN
DECLARE sChildList VARCHAR(10000);
DECLARE sChildTemp VARCHAR(1000);
--这里我第一次是要将传进来的GROUPCODE 转换成id,然后去递归查询
SELECT id INTO sChildTemp FROM grade_group WHERE GROUP_CODE = GROUPCODE;
WHILE sChildTemp IS NOT NULL DO
IF (sChildList IS NOT NULL) THEN
SET sChildList = CONCAT(sChildTemp,',',sChildList);
ELSE
SET sChildList = CONCAT(sChildTemp);
END IF;
SELECT GROUP_CONCAT(id) INTO sChildTemp FROM grade_group
WHERE FIND_IN_SET(PARENT_ID, sChildTemp)>0;
END WHILE;
RETURN sChildList;
END$$
DELIMITER ;