oracle递归查询父级或子级转换成mysql的语句

前言:今天公司做项目,遇到原项目是做oracle的数据库,然后现在用的是mysql。所以在转换的过程中遇到了oracle的递归语句报错,原因是mysql没有这个高级的递归查询语句,所以上网查询转换方法,网上各种存储过程,中间表什么的,搞的我脑袋瓜疼(突然感觉oracle收费还是有点道理的)。

问题:

select id from tm_org start with id=“ 402882e55f813b35015f816362c70003” 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 (“402882e55f813b35015f816362c70003”)
	)

数据表名: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 := “40288ac3643f18fe0164400878010017”, @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 := "402882e55f813b35015f816362c70003", @l := 0
			) vars, tm_org h
			WHERE @r <> 0
		) T1
			JOIN tm_org T2 ON T1._id = T2.id
	)
ORDER BY t.positionCode ASC;

运行截图:




  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值