前言：今天公司做项目，遇到原项目是做oracle的数据库，然后现在用的是mysql。所以在转换的过程中遇到了oracle的递归语句报错，原因是mysql没有这个高级的递归查询语句，所以上网查询转换方法，网上各种存储过程，中间表什么的，搞的我脑袋瓜疼（突然感觉oracle收费还是有点道理的）。
问题：
select id from tm_org start with id=“ e55f813b35015fc70003” connect by prior parent_id=id此语句parent_id写在左边就是向上查询父级，写在右边就是查询所有子级。
解决方法：
1.使用mysql查询所有子级：
DELIMITER $$ CREATE FUNCTION getChildrenOrg(orgid INT) RETURNS VARCHAR(1000) BEGIN DECLARE oTemp VARCHAR(1000); DECLARE oTempChild VARCHAR(1000); SET oTemp = ''; SET oTempChild = CAST(orgid AS CHAR); WHILE oTempChild IS NOT NULL DO SET oTemp = CONCAT(oTemp, ',', oTempChild); SELECT GROUP_CONCAT(id) INTO oTempChild FROM tm_org WHERE FIND_IN_SET(parent_id, oTempChild) > 0; END WHILE; RETURN oTemp; END $$ SELECT * FROM tm_org WHERE FIND_IN_SET( id, getChildrenOrg (“e55f813b35015fc70003”) )数据表名：tm_org,关键参与查询的字段，id、parent_id。
其中DELIMITER 定好结束符为"$$", 然后最后又定义为";", MYSQL的默认结束符为";".
创建函数如果在终端命令行或者在客户端执行sql语句的时候，有时候分号默认结束了，但是我们不希望结束，所以一定要使用这个结尾符，今天我就在这里卡了好久，以为无法创建函数。切记啊！
函数创建截图：
调用函数查询所有子级：
2.使用mysql查询所有父级。
mysql查询所有父级就是可以直接写sql语句了。
SELECT * FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM tm_org WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := “40288ac3643f18fe00017”, @l := 0) vars, tm_org h WHERE @r <> 0) T1 JOIN tm_org T2 ON T1._id = T2.id ORDER BY id;运行截图：
只需要把数据表的id写上去就行了。
结尾：附上今天我的修改并查询结果
代码：
SELECT DISTINCT t.*, t1.* FROM ( SELECT t.ID AS id, t.POSITION_CODE AS positionCode, t.POSITION_NAME AS positionName, t.POSITION_LEVEL AS positionLevel, t.ORG_ID AS orgId , t1.org_name AS orgName, t.PARENT_ID AS parentId, t2.position_name AS parentName, t.BUSINESS_GROUP AS businessGroup, t.HEADSTRING AS headString , t.ENABLE_STATUS AS enableStatus, t.UPDATE_NAME AS updateName, t.CREATE_NAME AS createName, t.UPDATE_DATE AS updateDate, t.CREATE_DATE AS createDate , t.EXT_CHAR_1 AS extChar1, t.EXT_CHAR_2 AS extChar2, t.EXT_CHAR_3 AS extChar3, t.EXT_CHAR_4 AS extChar4, t.EXT_CHAR_5 AS extChar5 , t.EXT_NUMBER_1 AS extNumber1, t.EXT_NUMBER_2 AS extNumber2, t.EXT_NUMBER_3 AS extNumber3, t.EXT_DATE_1 AS extDate1, t.EXT_DATE_2 AS extDate2 FROM tm_position t LEFT JOIN tm_org t1 ON t1.id = t.org_id LEFT JOIN tm_position t2 ON t2.id = t.parent_id WHERE 1 = 1 ) t LEFT JOIN ( SELECT p.id AS newId, o.org_name, o.id AS org_id, tmo.org_name AS parentOrgName, pp.position_code AS parentCode , trup.is_main AS isMain, trup.user_id, tu.username AS userName, tu.fullname AS fullName, ptu.fullname AS parentUserName , trup.id AS tmRPositionUserId FROM tm_position p LEFT JOIN tm_org o ON o.id = p.org_id LEFT JOIN tm_r_user_position trup ON trup.position_id = p.id LEFT JOIN tm_user tu ON tu.id = trup.user_id LEFT JOIN tm_position pp ON pp.id = p.parent_id LEFT JOIN tm_org tmo ON pp.org_id = tmo.id LEFT JOIN tm_r_user_position ptup ON pp.id = ptup.position_id LEFT JOIN tm_user ptu ON ptu.id = ptup.user_id ) t1 ON t1.newId = t.id WHERE 1 = 1 AND t.orgId IN ( SELECT id FROM ( SELECT @r AS _id , ( SELECT @r := parent_id FROM tm_org WHERE id = _id ) AS parent_id, @l := @l + 1 AS lvl FROM ( SELECT @r := "e55f813b35015fc70003", @l := 0 ) vars, tm_org h WHERE @r <> 0 ) T1 JOIN tm_org T2 ON T1._id = T2.id ) ORDER BY t.positionCode ASC;运行截图：
版权声明：
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若内容造成侵权、违法违规、事实不符，请将相关资料发送至xkadmin@xkablog.com进行投诉反馈，一经查实，立即处理！
转载请注明出处，原文链接：https://www.xkablog.com/sqlbc/10643.html