php - SQL add sub-index column on query?

Question

I have an SQL table in the following approximate format

+----+------------+------------+----------+------+
| id |  fin_date  | student_id | course_id| mark |
+----+------------+------------+----------+------+
|  1 | 2018-05-03 |      10049 |        1 |  60  |
|  2 | 2018-05-03 |      10032 |        2 |  45  |
|  3 | 2018-05-03 |      10032 |        1 |  88  |
|  4 | 2018-05-03 |      10032 |        1 |  96  |
+----+------------+------------+----------+------+

Unfortunately, I have just been asked to then have an index for a "student_course_id" sub-index that represents the number of courses that student had taken up until that time... Example below

+----+------------+------------+----------+------+-------------------+
| id |  fin_date  | student_id | course_id| mark | student_course_id |
+----+------------+------------+----------+------+-------------------+
|  1 | 2018-05-03 |      10049 |        1 |  60  |   1               |
|  2 | 2018-05-03 |      10032 |        2 |  45  |   1               |
|  3 | 2018-05-03 |      10032 |        3 |  88  |   2               |
|  4 | 2018-05-03 |      10032 |        4 |  96  |   3               |
|  5 | 2018-05-03 |      10049 |        2 |  60  |   2               |
+----+------------+------------+----------+------+-------------------+

This way when you lookup index id=3 between two dates, you then can immediately tell that its the 2nd course that student has taken.

I don't really want to add a new column to my table, is there any way to add this extra column in a similar way to COUNT(*) can be used to total unique fields in a single column?

标签： phpmysql

Answer 1

好吧，我想我问得有点过早了。我想到了...

SELECT *, ( select count(*) from `marks` t2 
where t1.student_id = t2.student_id and t1.id >= t2.id ) 
as student_course_id FROM `marks` as t1