MySQL 5.7 workarounds for ROW_NUMBER that don't use variables-CodePudding

Using variables, as seen here, to simulate the ROW_NUMBER function found in MySQL8 isn't an option when the query is being used to create a View.

What other kinds of workarounds are available for numbering rows if using variables is not an option?

I'll have to come up with a minimal example. But here's an idea of what I'm trying to do. I need find the time ranges between scheduled lesson events. The following produces the results I want, however, it uses variables to number by rows.

SELECT 
    L1.SCHEDULE_TYPE, L1.STUDIO_ID, L1.ROOM, L1.DAYOFWEEK, L1.END_TIME AS START_GAP, L2.START_TIME AS END_GAP
FROM 
    (SELECT 
        SCHEDULE_TYPE, STUDIO_ID, ROOM, DAYOFWEEK, TEACHER_ID, START_TIME, END_TIME
        ,(@row_number1 := @row_number1   1) AS ROW_NUMBER
        FROM 
            LESSON, (SELECT @row_number1 := 0) AS x
        ORDER BY 
            SCHEDULE_TYPE, STUDIO_ID, ROOM, DAYOFWEEK, START_TIME           
    ) AS L1
JOIN 
    (SELECT 
        SCHEDULE_TYPE, STUDIO_ID, ROOM, DAYOFWEEK, TEACHER_ID, START_TIME, END_TIME
        ,(@row_number2 := @row_number2   1) AS ROW_NUMBER
        FROM 
            LESSON, (SELECT @row_number2 := 0) AS x
        ORDER BY 
            SCHEDULE_TYPE, STUDIO_ID, ROOM, DAYOFWEEK, START_TIME
    ) AS L2 
ON(
    L1.SCHEDULE_TYPE = L2.SCHEDULE_TYPE
    AND L1.TEACHER_ID = L2.TEACHER_ID
    AND L1.DAYOFWEEK = L2.DAYOFWEEK
    AND L1.END_TIME < L2.START_TIME
    AND L1.ROW_NUMBER = (L2.ROW_NUMBER - 1)
)

CodePudding user response：

When you need to emulate

SELECT *, ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2)
FROM table

and do not use UDVs then you may use

SELECT *, ( SELECT COUNT(*) 
            FROM table t2 
            WHERE t1.col1 = t2.col1
              AND t1.col2 >= t2.col2 )
FROM table t1

SELECT t1.*, COUNT(t2.primary_key)
FROM table t1
JOIN table t2 ON t1.col1 = t2.col1
             AND t1.col2 >= t2.col2
GROUP BY t1.primary_key