读取row_number

debugcn 发表于 Dev

尼古拉斯（Nicholas）茶具套装

输出：https : //www.dropbox.com/s/q9bjrzndbzj0l2i/test3.PNG?dl=0

如果我想让Stud_ID = 4，仅想获得Position（row_number）= 3，如何将查询合并到当前代码中？

SET @row_num = 0; 
SELECT @row_num := @row_num + 1 as Position, s.* 
FROM
(
    SELECT
        Student.Stud_ID, Student.Stud_Name, Student.Stud_Class, SUM(Grade.Percentage) AS Points 
    FROM Student, Student_Subject, Grade 
    WHERE Student.Stud_ID = Student_Subject.Stud_ID 
        AND Student_Subject.Stud_Subj_ID = Grade.Stud_Subj_ID 
        AND Student.Stud_Form = '1' 
        AND Grade.Quarter = '1' 
    GROUP BY Student.Stud_ID 
    ORDER BY Points DESC
) AS s;

谢谢！！

哈里德·朱奈德（M Khalid Junaid）

在查询中使用子选择，并使用所需的行号对其进行过滤

SELECT * 
FROM (
SELECT @row_num := @row_num + 1 AS `Position`, s.* 
FROM
(
    SELECT
        Student.Stud_ID, Student.Stud_Name, Student.Stud_Class, SUM(Grade.Percentage) AS Points 
    FROM Student, Student_Subject, Grade 
    WHERE Student.Stud_ID = Student_Subject.Stud_ID 
        AND Student_Subject.Stud_Subj_ID = Grade.Stud_Subj_ID 
        AND Student.Stud_Form = '1' 
        AND Grade.Quarter = '1' 
    GROUP BY Student.Stud_ID 
    ORDER BY Points DESC
) AS s
CROSS JOIN (SELECT @row_num := 0) AS s1
) AS t 
WHERE t.Position = 3

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。