ORA-00979：不是在Oracle中表达的组

发布于 2025-01-18 02:14:53 字数 629 浏览 0 评论 0原文

我无法在 Oracle 上执行此代码，错误显示：

“ORA-00979：不是 GROUP BY 表达式”

但是，我能够在 MySQL 上成功运行它。
这是怎么发生的？

SELECT CONCAT(i.lname, i.fname) AS inst_name, 
           CONCAT(s.lname, s.fname) AS stu_name, 
           t.avg_grade AS stu_avg_grade
    FROM(
    SELECT instructor_id, student_id, AVG(grade) as avg_grade, RANK() OVER(PARTITION BY instructor_id ORDER BY grade DESC) AS rk
    FROM grade
    GROUP BY 1,2) t
    JOIN instructor i 
    ON t.instructor_id = i.instructor_id
    JOIN student s 
    ON s.student_id = t.student_id
    WHERE t.rk = 1
    ORDER BY 3 DESC

原文

I can not execute this code on Oracle, the error shows:

"ORA-00979: not a GROUP BY expression"

However, I was able to run it successfully on MySQL.
How does this happen?

SELECT CONCAT(i.lname, i.fname) AS inst_name, 
           CONCAT(s.lname, s.fname) AS stu_name, 
           t.avg_grade AS stu_avg_grade
    FROM(
    SELECT instructor_id, student_id, AVG(grade) as avg_grade, RANK() OVER(PARTITION BY instructor_id ORDER BY grade DESC) AS rk
    FROM grade
    GROUP BY 1,2) t
    JOIN instructor i 
    ON t.instructor_id = i.instructor_id
    JOIN student s 
    ON s.student_id = t.student_id
    WHERE t.rk = 1
    ORDER BY 3 DESC

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鹿港小镇 2025-01-25 02:14:53

您不能在Oracle中使用1,2 之类的序数。此外，按等级子句在您的等级（）函数都有问题。请记住，分析功能在集合之后评估，因此不再可用等级。这是一个应该无错误的版本：

SELECT CONCAT(i.lname, i.fname) AS inst_name,
       CONCAT(s.lname, s.fname) AS stu_name,
       t.avg_grade AS stu_avg_grade
FROM
(
    SELECT instructor_id, student_id, AVG(grade) AS avg_grade,
           RANK() OVER (PARTITION BY instructor_id ORDER BY AVG(grade) DESC) AS rk
    FROM grade
    GROUP BY instructor_id, student_id
 ) t
INNER JOIN instructor i 
    ON t.instructor_id = i.instructor_id
INNER JOIN student s 
    ON s.student_id = t.student_id
WHERE t.rk = 1
ORDER BY t.avg_grade DESC;

You can't use ordinals like GROUP BY 1,2 in Oracle. In addition, the ORDER BY grade clause inside your RANK() function has a problem. Keep in mind that analytic functions evaluate after the GROUP BY aggregation, so grade is no longer available. Here is a version which should work without error:

SELECT CONCAT(i.lname, i.fname) AS inst_name,
       CONCAT(s.lname, s.fname) AS stu_name,
       t.avg_grade AS stu_avg_grade
FROM
(
    SELECT instructor_id, student_id, AVG(grade) AS avg_grade,
           RANK() OVER (PARTITION BY instructor_id ORDER BY AVG(grade) DESC) AS rk
    FROM grade
    GROUP BY instructor_id, student_id
 ) t
INNER JOIN instructor i 
    ON t.instructor_id = i.instructor_id
INNER JOIN student s 
    ON s.student_id = t.student_id
WHERE t.rk = 1
ORDER BY t.avg_grade DESC;

回复收藏 0 原文

~没有更多了~