mysql 数据透视查询和排名查询

Question

我有一个像这样的标记表:

ID  STUDENT_ID  Branch_id   Class_id    Exam_id Subject_id  Numbers     Date1       653         5           1           1   8               60      2012-01-012       653         5           1           1   9               40      2012-01-013       653         5           1           1   10              80      2012-01-014       653         5           1           1   11              50      2012-01-015       653         5           1           1   12              65      2012-01-016       653         5           1           1   13              33      2012-01-017       653         5           1           1   15              86      2012-01-018       222         5           1           1   8               100     2012-01-019       222         5           1           1   9               80      2012-01-0110      222         5           1           1   10              92      2012-01-0111      222         5           1           1   11              50      2012-01-0112      222         5           1           1   12              65      2012-01-0113      222         5           1           1   13              33      2012-01-017       222         5           1           1   15              86      2012-01-01

I want to get rank I got answer by this question

Also when I fetched all class result I use pivot query:

SELECT stu_id, sum(numbers) AS total, branch_id, depart_id, class_id,
 SUM( IF( subject_id =1, numbers, 0 ) ) AS MAth,
 SUM( IF( subject_id =2, numbers, 0 ) ) AS Eng,
SUM( IF( subject_id =3, numbers, 0 ) ) AS Science
FROM marksheet where branch_id = 1 AND depart_id = 1
 AND class_id = 1 GROUP BY stu_id ORDER BY total DESC

我想在我的类(class)查询(数据透视查询)中获得排名？我想数一数有多少学生排在第一位，有多少学生排在第二和第三位？

所需数据样本:

ID      Name        Math        English     Science     Total   Percent     Position   Rank

有人帮忙吗？

Answer 1

我认为您需要做的是创建第二个表，其中包含正在引用的年级边界，例如:

ID    grade    start_boundry    end_boundry
1       A             60            100     

等..

然后在表之间创建连接，然后在数字和开始/结束边界之间执行 WHERE 语句

所以 ->

SELECT grade FROM boundries_table RIGHT JOIN  sudent_table
WHERE boundries_table.start_boundry < student_table.numbers
AND boundries_table.end_boundry > student_table.numbers

我认为如果我的 MySQL 内存可用，那应该可以工作，只需将表修改为您需要它运行的方式，它应该可以满足您的需要。