MYSQL 查询不工作，对于这种情况是否有更简单的查询？

Question

我有一个 STUDENT 表，其中包含有关教职工学生的数据:

STUDENT(ID, Name, Grade, ID_Tutor)

结构和数据:

CREATE TABLE STUDENT
    (`ID` int, `Name` varchar(5), `Grade` int, `ID_Tutor` int);

INSERT INTO STUDENT
    (`ID`, `Name`, `Grade`, `ID_Tutor`)
VALUES
    (1,    'A',     10,       NULL),
    (2,    'B',     9.5,      1),
    (3,    'C',     9,        1),
    (4,    'D',     8,        1),
    (5,    'E',     7,        4),
    (6,    'F',     8,        1),
    (7,    'G',     5,        7),
    (10,   'H',     6,        5)
    (11,   'I',     7,        3),
    (12,   'J',     9,        10),
    (13,   'K',     9.6,      11),
    (14,   'L',     8.9,      5),
    (15,   'M',     9.9,      13),
    (16,   'N',     7,        2),
    (17,   'O',     7.9,      2),
    (18,   'P',     2,        15);

前 5 教师成绩:

    `ID`, `Name`, `Grade`, `ID_Tutor`
    (1,    'A',     10,       NULL),
    (15,   'M',     9.9,      13),
    (13,   'K',     9.6,      11),
    (2,    'B',     9.5,      1),
    (3,    'C',     9,        1),
    (12,   'J',     9,        10),

在结果中应该出现上表中这些导师的学生。

我想做一个SQL查询(MYSQL)来查找

• 学生姓名,
• 导师姓名和
• 导师等级

对于导师在前 5 教员中的成绩的学生(并且不使用LIMIT 和/或ROWNUM - 对于 Oracle 解决方案)。

结果应该是这样的:

| student name | tutor name | tutor grade |
       'B'           'A'          10           
       'C'           'A'          10           
       'D'           'A'          10           
       'F'           'A'          10           
       'P'           'M'           9.9           
       'M'           'K'           9.6              
       'N'           'B'           9.5            
       'O'           'B'           9.5            
       'I'           'C'           9            

我尝试过类似的方法(但不 工作)，我认为我很复杂......(我不应该使用 ROWNUM)

SELECT 
    s.ID, 
    s.Name Student, 
    s.Grade, 
    p.ID_Tutor, 
    p.Grade
FROM 
    STUDENT s,
    (SELECT * FROM 
        (SELECT * FROM 
            (SELECT 
                t.Name Tutor , 
                t.ID , 
                Grade Grade_Tutor 
            FROM STUDENT s
                JOIN STUDENT t 
                    on s.ID_Tutor = t.ID
            GROUP BY t.Name, t.ID)
        ORDER BY 3 desc ) 
    WHERE ROWNUM < 6) p
WHERE s.ID_Tutor = p.ID

给我这个错误:

    #1248 - Every derived table must have its own alias

提前致谢!

Answer 1

您的代码缺少多个表别名。检查这个工作 SQL Fiddle code .但它不会返回任何值，因为我只是添加了一些愚蠢的值。请随意使用它并测试您的结果。

SELECT s.ID, s.Name Student, s.Grade, p.ID_Tutor, p.Grade_tutor
FROM STUDENT s,
    (SELECT * FROM 
        (SELECT * FROM 
            (SELECT t.Name Tutor, t.ID , s.Grade Grade_Tutor, s.ID_Tutor
               FROM STUDENT s
               JOIN STUDENT t
                    on s.ID_Tutor = t.ID
            GROUP BY t.Name, t.ID) innerTable
         ORDER BY 3 desc ) secondInnerTable
     WHERE @ROWNUM < 6) p
WHERE s.ID_Tutor = p.ID