return mySQL original row number

问题:

I want to get the row_number of the original record in the database. Is there any way to retrieve the original row number of a record in mysql? Suppose my statement is

select course_id from course where subject='finance';

there can be multiple records for this query. Suppose they are from row numbers 4, 5, 7, 9 and 10. How can i retrieve these row_numbers from my query?

回答1:

Check: With MySQL, how can I generate a column containing the record index in a table?

SELECT  c.course_id, 
        @curRow := @curRow + 1 AS row_number
FROM    course c
JOIN    (SELECT @curRow := 0) r;

Do note however, this is artificial as the only real 'row number' would be your own primary ID, and every artificial method will change the row number when you change the ORDER clause