oracle函数dense_rank(),row_number(),connect_by_root(),start with connect by 用mysql如何实现

之前项目要求要把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_NUMBER = T1.REPLACE_PART_NO) T
  LEFT JOIN T_P PART1
    ON T.PART_NUMBER1 = PART1.PART_NUMBEr)
 WHERE 1 = 1
   AND T.PART_NUMBER2 IS NOT NULL;

改写成mysql的存储过程实现如下：

DECLARE tree_level INT DEFAULT 1;
DECLARE rootValue VARCHAR(100);
#TM_SBOM_AE_PART_ID
#创建临时表（因为存储过程中不能多次调用一张临时表，所以要创建多张临时表）
DROP TEMPORARY TABLE IF EXISTS tmp_tree_TM_SBOM_AE_PART;
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_tree_TM_SBOM_AE_PART
(
    id BIGINT(20),
PART_NUMBER VARCHAR(20),
REPLACE_PART_NO varchar(20),
level TINYINT(4),
path VARCHAR(2100)#id连接起来的path，假设树最大有100层
);
DROP TEMPORARY TABLE IF EXISTS t_t_t_T_S_A_P;
CREATE TEMPORARY TABLE IF NOT EXISTS t_t_t_T_S_A_P
(
id BIGINT(20),
PART_NUMBER VARCHAR(20),
REPLACE_PART_NO varchar(20),
level TINYINT(4),
path VARCHAR(2100)#id连接起来的path，假设树最大有100层
);
DROP TEMPORARY TABLE IF EXISTS t_t_c_T_S_A_P;
CREATE TEMPORARY TABLE IF NOT EXISTS t_t_c_T_S_A_P
(
id BIGINT(20),
PART_NUMBER VARCHAR(20),
REPLACE_PART_NO varchar(20),
level TINYINT(4),
path VARCHAR(2100)#id连接起来的path，假设树最大有100层
);


INSERT INTO t_t_T_S_A_P
SELECT T1.TM_SBOM_AE_PART_ID,  
        T1.PART_NUMBER PART_NUMBER1,
T1.REPLACE_PART_NO,
        tree_level,
CONCAT("/",T1.TM_SBOM_AE_PART_ID)
  FROM (SELECT SP.TM_PART_ID,SP.REPLACE_PART_NO,
          PART.PART_NUMBER,PART.PART_NAME,SP.INTERCHANGE_TYPE , SP.REPLACE_PART_DATE , SP.TM_SBOM_AE_PART_ID 
        FROM T_S_A_P S INNER JOIN T_P PART ON SP.TM_PART_ID = PART.TM_PART_ID WHERE 1 = 1 
  ) T1 WHERE T1.REPLACE_PART_NO IS NULL;
INSERT INTO t_t_c_T_S_A_P
SELECT * FROM t_t_T_S_A_P;
 
WHILE ROW_COUNT()>0 DO
SET tree_level=tree_level+1;
INSERT INTO t_t_t_T_S_A_P
SELECT T1.TM_SBOM_AE_PART_ID,  
        T1.PART_NUMBER PART_NUMBER1,
T1.REPLACE_PART_NO,
        tree_level,
CONCAT(tree.path,"/",T1.TM_SBOM_AE_PART_ID)
FROM (SELECT SP.TM_PART_ID,SP.REPLACE_PART_NO,
                PART.PART_NUMBER,PART.PART_NAME,SP.INTERCHANGE_TYPE , SP.REPLACE_PART_DATE , SP.TM_SBOM_AE_PART_ID 
            FROM T_S_A_ SP INNER JOIN T_P PART ON SP.TM_PART_ID = PART.TM_PART_ID WHERE 1 = 1 
      ) T1,t_t_TS_A_P tree
WHERE T1.REPLACE_PART_NO=tree.PART_NUMBER
AND tree.level=tree_level-1
AND NOT EXISTS(
SELECT * from t_t_c_T_S_A_P tr
WHERE tr.id=T1.TM_SBOM_AE_PART_ID
);
 
INSERT INTO t_t_c_T_S_A_P
SELECT * FROM t_t_t_T_S_A_P part WHERE part.level=tree_level;
INSERT INTO t_t_T_S_A_P
SELECT * FROM t_t_t_T_S_A_P part WHERE part.level=tree_level;


END WHILE;


#select * from t_t_T_S_A_P WHERE LEVEL=1;
 
SET @root:=null;
#row_number()over(PARTITION by ORDER BY )分组排序
SET @row_rank:=0;
SET @rownum:=0;
#改写oracle的dense_rank()over(ORDER BY )密集型排序
SET @dense_rank:=-1;
SET @root_dense:=-1;


SET @result_sql=CONCAT("select @rownum:=@rownum+IF(@root_dense=T.ROOT,0,1) as dense_rank,
T.PART_NAME as PART_NAME1,
IF(@root=T.ROOT,@row_rank:=@row_rank+1,@row_rank:=1 )as row_rank
  from(
      select SUBSTRING(SUBSTRING_INDEX(tree.path,'/',2),2)as ROOT,T1.PART_NAME PART_NAME,tree.LEVEL MYLEVEL,T1.PART_NUMBER PART_NUMBER1, 
             T1.REPLACE_PART_NO PART_NUMBER2,T1.INTERCHANGE_TYPE,T1.REPLACE_PART_DATE REPLACE_PART_DATE,T1.TM_SBOM_AE_PART_ID
      from t_t_T_S_A_P tree,
             ( SELECT SP.TM_PART_ID,SP.REPLACE_PART_NO,
               PART.PART_NUMBER,PART.PART_NAME,SP.INTERCHANGE_TYPE , SP.REPLACE_PART_DATE , 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 where tree.id= T1.TM_SBOM_AE_PART_ID order by tree.path
  )T LEFT JOIN T_P PART1 ON T.PART_NUMBER1 = PART1.PART_NUMBER )“，”
     WHERE 1 = 1 AND T.PART_NUMBER2 IS NOT NULL 
     AND EXISTS( SELECT 1 FROM T_S_A_P PU WHERE PU.TM_SBOM_AE_PART_ID = T.TM_SBOM_AE_PART_ID ");  
PREPARE resultSql FROM @result_sql;
EXECUTE resultSql;
END

以上仅供参考，谢谢！