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" columnCOALESCE()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