php - mysql根据另一个表将序列更新到一个表中

Question

下面是 2 个表格:

类(class)

            academic_year_id    student_id      standard

                    2               1               10
                    2               2               10
                    2               3               10
                    2               4               10
                    2               5               10
                    2               6               10
                    2               7               11 Science
                    2               8               11 Science

这里的标准不是INT

学生

            student_id      roll_no         name            teacher_approval

                1               0       S Sumeet G                  1
                2               0       Nair Nikhil R               1
                3               0       Nayak Ankita R              0
                4               0       Rathod Ketan P              0
                5               0       Patel Vishal D              1
                6               0       Patel Jignesh R             0
                7               0       Prajapati Bhavesh A         1
                8               0       Shah Harsh N                1

我想做什么:

当教师选择标准 -> 10 并按下“分配卷号”按钮时

我希望所有 10 年级的学生都获得按姓名顺序排列的卷，并且得到老师的批准，即 Teacher_approval = '1'

所以我的学生表如下:

学生

            student_id      roll_no         name            teacher_approval

                1               3       S Sumeet G                  1
                2               1       Nair Nikhil R               1
                3               0       Nayak Ankita R              0
                4               0       Rathod Ketan P              0
                5               2       Patel Vishal D              1
                6               0       Patel Jignesh R             0
                7               0       Prajapati Bhavesh A         1
                8               0       Shah Harsh N                1

到目前为止我已经尝试过以下代码:

$standard = $_POST['standard']

SET @incr = 0

     UPDATE
        student AS st
        JOIN
        (   SELECT * FROM
        classes AS classes
        WHERE standard = '".$standard."'
        ) AS tmp
        ON tmp.student_id = st.id
        SET
        st.roll_no = @incr:=@incr+1 
        WHERE st.teacher_approval = '1'
        ORDER BY st.name ASC

但它给了我一个错误:更新和排序依据的使用不正确

大家有类似经历吗？任何帮助，将不胜感激。谢谢。

Answer 1

在 MySql 中，使用多个表时不能直接将 ORDER BY 作为 UPDATE 的一部分(请参阅此 link )。试试这个:

UPDATE student AS table1
        INNER JOIN (
        SELECT st.student_id, st.roll_no, st.name, st.teacher_approval
        FROM student AS st
            JOIN (
            SELECT * FROM
            classes AS cl
            WHERE cl.standard = '".$standard."'
            ) AS tmp
            ON tmp.student_id = st.student_id
            WHERE st.teacher_approval = '1'
            ORDER BY st.name ASC
        ) AS table2 
        ON table2.student_id = table1.student_id
    SET table1.roll_no = @incr:=@incr+1