Convert some statements from Oracle to MySQL

A few days ago, the system was migrated from oracle to mysql. Many statements are relatively simple, that is, some function modifications, such as to-date.

A few days ago, the system was migrated from oracle to mysql. Many statements are relatively simple, that is, some function modifications, such as to-date.

Convert some statements from Oracle to MySQL

[Date:] Source: Linux community Author: jimmy609 [Font:]

A few days ago, the system was migrated from Oracle to mysql. Many statements are relatively simple, that is, some function modifications, such as to-date.

But there are also a few tricky ones, which are recorded here

1. row_number () over (partition

First, let's take a look at the usage of this function in oracle.

Select t. *, row_number () over (partition by t. owner order by y. createDate desc) rn from test t

This statement indicates that the data in the test table is grouped by the owner and a serial number is added to the data in each group. The data format is as follows:

Id name owner createDate rn

1 aa 001 1

2 bb 001 2

3 cc 001 3

4 dd 002 1

5 ee 002 2

6 ff 003 1

Data is not formal, but you should be able to understand what it means,

However, this function does not exist in mysql. Find and find a solution, as shown below:

SELECT
Heyf_tmp .*,
IF (@ pdept = heyf_tmp.owner, @ rn: = @ rn + 1, @ rn: = 1) AS rn,
@ Pdept: = heyf_tmp.owner
FROM
(
SELECT
Yv .*
FROM
Test yv
ORDER
Yv. owner,
Yv. createDate DESC
)
Heyf_tmp,
(
SELECT
@ Rn: = 0,
@ Pdept: = NULL,
@ Rn: = 0
)
Aa

What does it mean? It's not very clear, but let's solve the problem first.

2. oracle tree Query

Existing methods for oracle tree Query

Select distinct t. id as id, t. name

From test t
Start with id =''
Connect by prior id = parentid

However, this method is not available in mysql, so you can only define functions or processes by yourself. Here I use the process

As follows:

Create procedure Pro_GetTreeList '(in pid varchar (36 ))
Begin
Declare levint;
Set lev_= 1;
Drop table if exists tmp1;
Create table tmp1 (id VARCHAR (40), name varchar (50), parentid varchar (40), levv INT );
INSERT tmp1 SELECT id, name, parent_id, 1 FROM 'test' WHERE parent_id = pid;
While row_count ()> 0
Do set maid + 1;
INSERT tmp1 SELECT t. id, t. name, t. parent_id, levfrom testt join tmp1 a on t. parent_id = a. id AND levv = lev-1; -- locate the child node
End while;
INSERT tmp1 SELECT id, name, parent_id, 0 FROM test WHERE id = pid; -- find the current node
End

You can understand this stored procedure.