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

Question

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

我需要创建一个查询，返回每个学生在类(class)中的 ID 和位置(排名)；该位置根据存储在 academic_averages 表中的学术平均值降序排列。

(例如第一个来自第 1 类，第二个来自第 1 类，依此类推...第一个来自第 2 类，第二个来自第 2 类...)

这是查询:

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，因为它会引发错误(未知列)。

我试过使用 INNER JOIN 而不是子查询，但到目前为止运气不好。

有没有人有解决办法？

谢谢

LE:在性能方面必须优化查询

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 fiddle 似乎存在一些问题，同时这里是架构: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 (按类(class)要求的学生排名)评论中的阐述进行编辑

此查询应按类(class)为您提供学生的职位。如果您想删除某些字段，可以将 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