How can I replace nested for statement with mysql query?-CodePudding

Environment :

MySQL 5.7.x
Spring MVC

Table Data (name: TableA)

seq	level	name	order	parent_seq
1	1	name1	1	0
2	1	name2	2	0
3	2	sub1-1	1	1
4	2	sub1-2	2	1
5	2	sub2-1	1	2
6	3	third-2-1	1	5
7	3	third-1-1	1	3

Expected Result

seq	level	name	order	parent_seq	next_level
1	1	name1	1	0	2
3	2	sub1-1	1	1	3
7	3	third-1-1	1	3	2
4	2	sub1-2	2	1	1
2	1	name2	2	0	2
5	2	sub2-1	1	2	3
6	3	third-2-1	1	5	1 (last default value: 1)

Now I'm genenrating expected result with nested for statement(JAVA). Is there any way to generate expected result only with MySQL Query?

Thanks in advance!

CodePudding user response：

Summary:

Use REGEXP_SUBSTR(name,"[0-9] \-?[0-9]*") to extract the numbers and sort the datas using the numbers.
For MySQL v8 above, you can use LEAD() to generate the "next_level" column based on the "level" column
COALESCE() function for the last default value = 1

SELECT 
    t1.*, 
    COALESCE(LEAD(t1.level, 1) OVER(ORDER BY REGEXP_SUBSTR(name,"[0-9] \-?[0-9]*")), 1) AS next_level
FROM TableA t1
ORDER BY REGEXP_SUBSTR(name,"[0-9] \-?[0-9]*"), t1.level

See db<>fiddle