用于复杂查询的MySQL子查询未知列

Question

我有下面的sqlfiddle http://sqlfiddle.com/#!2/324628/1

我需要创建一个查询，返回班级中每个学生的id和职位(排名)；该职位按照存储在academic_averages表中的学术平均值的值进行降序排序。

(例如，第一类来自第一类，第二个来自第一类，等等).第一班来自第二班，第二名来自第二班.)

以下是查询：

SELECT students.id,

(SELECT x.position
FROM (
    SELECT t.student_id, t.value, @rownum := @rownum + 1 AS position
    FROM (
        SELECT aa.student_id, aa.value
        FROM academic_averages AS aa
        INNER JOIN students AS s ON s.id = aa.student_id
        INNER JOIN classes_students AS cs ON cs.student_id = s.id
        INNER JOIN classes_academic_years AS cas ON cas.id = cs.class_academic_year_id

        INNER JOIN classes_academic_years as cas2 on cas2.class_id = cas.class_id
        INNER JOIN classes_students as cs2 on cs2.class_academic_year_id = cas2.id
        INNER JOIN students as s2 on s2.id = cs2.student_id

        WHERE s2.id = 243
        AND cas.academic_year_id = 4
        AND aa.academic_year_id = 4

        GROUP BY aa.student_id
        ORDER BY abs(aa.value) DESC
    ) t 
    JOIN (SELECT @rownum := 0) r 
) AS x WHERE x.student_id = students.id ) AS ranking_by_class

FROM students

但是，由于它包含一个子查询，所以我不能将WHERE从最内部的查询更改为s2.id = students.id，因为它会引发一个错误(未知列)。

我尝试过使用内部连接而不是子查询，但到目前为止没有任何进展。

有人有解决办法吗？

谢谢

LE:从性能上讲，必须优化查询

莱:这是表格的结构：

academic_averages

id
student_id
value
academic_year_id

classes_academic_years

id
class_id
name
grade
academic_year_id

classes_students

id
class_academic_year_id
student_id

类

id
school_id

学生

id

所需的输出应该是student_id, position。sql小提琴似乎存在一些问题，同时，下面是一个模式：http://snippi.com/s/db8za8k

Answer 1

SELECT x.id
  , x.position
  , x.academic_average
FROM (SELECT
  s.id
  , @rownum := @rownum + 1 position
  , av.value academic_average
FROM students s
JOIN classes_students cs ON s.id = cs.student_id 
JOIN classes_academic_years cay ON cay.id = cs.class_academic_year_id
JOIN academic_averages av ON av.student_id = s.id
WHERE cay.academic_year_id = 4 -- change these two parameters in    
  AND  av.academic_year_id = 4 -- the subquery for different years
ORDER BY av.value DESC) x,
(SELECT @rownum := 0) y
ORDER BY academic_average DESC

我认为上面的查询应该适用于您。

我假设学术排名position是按学术平均值的递减顺序确定的。

我无法访问您的数据集，因此我增加了三行额外的行，其中两行用于选择学生的平均成绩，另一条则根据学术平均数按降序排列结果。这将帮助您验证它是否正常工作。如果您运行了该查询，并且它可以工作，那么它应该使用position显示记录，从1开始，然后递增1。

在生产中，为了得到您指定的结果集，我将省略这些片段：

1. , x.academic_average
2. , av.value academic_average
3. ORDER BY academic_average DESC

按OP (班级要求的学生排名)在评论中编辑以下详细说明

这个查询应该按班级给出学生的位置。如果要删除某些字段，可以将SELECT包装在另一个SELECT中，或者在将数据集提取到另一种语言后忽略这些列。

SELECT 
    x.student_id
  , x.cay_class_id
  , x.academic_average
  , if(@classid = x.cay_class_id, @rownum := @rownum + 1, @rownum := 1) position
  , @classid := x.cay_class_id
FROM (SELECT
    s.id student_id
  , cay.class_id cay_class_id
  , av.value academic_average

FROM students s
JOIN classes_students cs ON s.id = cs.student_id 
JOIN classes_academic_years cay ON cay.id = cs.class_academic_year_id
JOIN academic_averages av ON av.student_id = s.id
WHERE cay.academic_year_id = 4 -- change these two parameters in    
  AND  av.academic_year_id = 4 -- the subquery for different years
ORDER BY cay.class_id, av.value DESC) x,
(SELECT @classid := 0, @rownum := 0) y