t**********s
发帖数: 930 | 1 我有一个学生学号,班号,和成绩的表,现在我想写个MySql的 query,首先将班号排序,其
次在同班内将成绩排序,最后想加个rank域,将同班内成绩的ranking写出来.
前两个好办,用ORDER BY 就行,第三个要求怎么完成呢?
谢谢! | t*****g
发帖数: 1275 | 2 SELECT t1.id_student, t1.id_class, t1.grade,
(SELECT count(*) + 1 from mytalbe t2 WHERE t2.id_class = t1.id_class and t2
.grade > t1.grade)
FROM mytable t1 order by t1.id_class, t1.grade desc;
【在 t**********s 的大作中提到】
: 我有一个学生学号,班号,和成绩的表,现在我想写个MySql的 query,首先将班号排序,其
: 次在同班内将成绩排序,最后想加个rank域,将同班内成绩的ranking写出来.
: 前两个好办,用ORDER BY 就行,第三个要求怎么完成呢?
: 谢谢!
| t**********s
发帖数: 930 | 3 谢谢!
我又加了相同成绩并列名次的处理.
SELECT S.ID_STUDENT, S.ID_CLASS,S.GRADE, COUNT(*) AS RANK FROM STUDENTS AS S
LEFT OUTER JOIN STUDENTS AS S1 ON S.ID_CLASS = S1.ID_CLASS AND (((S.GRADE =
S1.GRADE) AND (S.ID_STUDENT=S1.ID_STUDENT)) OR (S.GRADE < S1.GRADE) ) GROUP
by S.ID_CLASS, S.GRADE DESC, S.ID_STUDENT;
t2
【在 t*****g 的大作中提到】
: SELECT t1.id_student, t1.id_class, t1.grade,
: (SELECT count(*) + 1 from mytalbe t2 WHERE t2.id_class = t1.id_class and t2
: .grade > t1.grade)
: FROM mytable t1 order by t1.id_class, t1.grade desc;
| t*****g
发帖数: 1275 | 4 How does this work?
S
GROUP
【在 t**********s 的大作中提到】
: 谢谢!
: 我又加了相同成绩并列名次的处理.
: SELECT S.ID_STUDENT, S.ID_CLASS,S.GRADE, COUNT(*) AS RANK FROM STUDENTS AS S
: LEFT OUTER JOIN STUDENTS AS S1 ON S.ID_CLASS = S1.ID_CLASS AND (((S.GRADE =
: S1.GRADE) AND (S.ID_STUDENT=S1.ID_STUDENT)) OR (S.GRADE < S1.GRADE) ) GROUP
: by S.ID_CLASS, S.GRADE DESC, S.ID_STUDENT;
:
: t2
| t**********s
发帖数: 930 | 5 注意LEFT OUTER JOIN 的条件.每个tuple的grade只和比它大或相等的grade结合.
于是第一名的成绩只和它自己结合,第二名的成绩和它自己并和比它高的第一名成绩结
合.
越后的成绩结合的次数越多,正好和它的rank相等.用COUNT和GROUP BY就能算出名次.
【在 t*****g 的大作中提到】
: How does this work?
:
: S
: GROUP
| t*****g
发帖数: 1275 | 6 I'm still lost :) Were you able to run your query against a real database
and get the result you want?
【在 t**********s 的大作中提到】
: 注意LEFT OUTER JOIN 的条件.每个tuple的grade只和比它大或相等的grade结合.
: 于是第一名的成绩只和它自己结合,第二名的成绩和它自己并和比它高的第一名成绩结
: 合.
: 越后的成绩结合的次数越多,正好和它的rank相等.用COUNT和GROUP BY就能算出名次.
| t**********s
发帖数: 930 | 7 Yes. It worked in my sql.
我参考的是这个link:
http://www.xaprb.com/blog/2005/09/27/simulating-the-sql-row_number-function/
【在 t*****g 的大作中提到】
: I'm still lost :) Were you able to run your query against a real database
: and get the result you want?
| c***r
发帖数: 65 | 8 select ......,row_number() over (partition by 班号 order by 成绩 desc) as
rank from xxx | f********t
发帖数: 74 | 9 It depends on what database you work on. The last post should be good
example in teradata. I do not think it is ansi standard, but I might be
wrong. |
|
