没有看到记录及要求结果,TRY: SELECT A.student_id,A.class_id,A.score FROM TT A LEFT JOIN TT B ON A.class_id=B.class_id AND A.SCORE<=B.SCORE GROUP BY A.student_id,A.class_id,A.score HAVING COUNT(B.class_id)<=2
大哥的SQL果然强悍。。但是这个SQL。。要建立2个索引(student_id,class_id,score )和(class_id,score )可能有些不值得。。其实小弟的简单看法是,select * from tt order by score limit 15 然后再程序里筛选,不够10条数据就再拿几条。。select * from tt order by score limit 15,5
估计~SQL写出来了性能也不怎样。。
SELECT A.student_id,A.class_id,A.score
FROM TT A LEFT JOIN TT B
ON A.class_id=B.class_id AND A.SCORE<=B.SCORE
GROUP BY A.student_id,A.class_id,A.score
HAVING COUNT(B.class_id)<=2
然后再程序里筛选,不够10条数据就再拿几条。。select * from tt order by score limit 15,5