之前项目要求要把oracle换成mysql,这样一来oracle中的好多函数都需要使用MySQL的存储过程来实现。网上搜到的资料太单一,千篇一律。经过参考好多资料,最后整理出跟我业务相关的几个函数:dense_rank(),row_number(),connect_by_root(),start with connect by。下面直接使用实例演示:
要改写的oracle脚本大致是这样的:
SELECT DENSE_RANK() OVER(ORDER BY T.ROOT),
T.PART_NAME as PART_NAME1,
ROW_NUMBER() OVER(PARTITION BY T.ROOT ORDER BY T.MYLEVEL DESC)
FROM (SELECT CONNECT_BY_ROOT(T1.PART_NUMBER) ROOT,
T1.PART_NAME PART_NAME,
LEVEL MYLEVEL,
T1.TM_SBOM_AE_PART_ID
FROM (SELECT SP.TM_PART_ID,
SP.TM_SBOM_AE_PART_ID
FROM T_S_A_P SP
INNER JOIN T_P PART
ON SP.TM_PART_ID = PART.TM_PART_ID
WHERE 1 = 1
) T1
START WITH T1.REPLACE_PART_NO IS NULL
CONNECT BY NOCYCLE PRIOR T1.PART_NUMBE