我可以用加入使用案例语句吗？

发布于 2025-01-19 12:48:08 字数 895 浏览 0 评论 0原文

我有 2 个带有值的表：

CREATE TABLE students (
   ids int AUTO_INCREMENT PRIMARY KEY,
   name varchar(100) DEFAULT NULL,
   surname varchar(100) DEFAULT NULL
);

CREATE TABLE register (
   idr int AUTO_INCREMENT PRIMARY KEY,
   data_ora datetime DEFAULT NULL,
   idstu int DEFAULT NULL,
   grade int DEFAULT NULL,
   INDEX idstu (idstu),
);

请求是：2015 年学生成绩列表，取决于平均值（10, 9 - 非常好，8,7 - 好，6,5 - 令人满意，4-0 - 不好）

我想使用 CASE 语句，但我不知道如何将它与 JOIN 语句一起使用。

我尝试了这个：

SELECT name, surname, ROUND(avg(nota),2) as average
JOIN register ON students.ids = register.idstu,
CASE average
WHEN 10 THEN 'Very good'
WHEN 9 THEN 'Very good'
WHEN 8 THEN 'Good'
WHEN 7 THEN 'Good'
WHEN 6 THEN 'satisfactorily'
WHEN 5 THEN 'satisfactorily'
ELSE 'Not good'
END AS mark
FROM students
WHERE YEAR(data_ora) = 2015;

但我收到一个错误 - 字段列表中未知的“平均”列。

有什么想法吗？

原文

I have 2 tables with values:

CREATE TABLE students (
   ids int AUTO_INCREMENT PRIMARY KEY,
   name varchar(100) DEFAULT NULL,
   surname varchar(100) DEFAULT NULL
);

CREATE TABLE register (
   idr int AUTO_INCREMENT PRIMARY KEY,
   data_ora datetime DEFAULT NULL,
   idstu int DEFAULT NULL,
   grade int DEFAULT NULL,
   INDEX idstu (idstu),
);

And the request is: the list of students' grades in 2015, depending on the average (10, 9 - Very good, 8,7 - Good, 6,5 - satisfactorily, 4-0 - not good)

I would like to use the CASE statement but I don't know how to use it with a JOIN statement.

I tried this:

SELECT name, surname, ROUND(avg(nota),2) as average
JOIN register ON students.ids = register.idstu,
CASE average
WHEN 10 THEN 'Very good'
WHEN 9 THEN 'Very good'
WHEN 8 THEN 'Good'
WHEN 7 THEN 'Good'
WHEN 6 THEN 'satisfactorily'
WHEN 5 THEN 'satisfactorily'
ELSE 'Not good'
END AS mark
FROM students
WHERE YEAR(data_ora) = 2015;

But I get an error - unknown column 'average' in the field list.

Any ideas?

分享到QQ

分享到微博

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

发布评论

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

猫瑾少女 2025-01-26 12:48:08

我认为您的查询顺序不正确。正确的查询应该类似 -

SELECT name, surname, CASE WHEN ROUND(avg(nota),2) >= 9 THEN 'Very good'
                           WHEN ROUND(avg(nota),2) >= 7 THEN 'good' 
                           WHEN ROUND(avg(nota),2) >= 5 THEN 'satisfactorily'
                           ELSE 'Not good' END AS mark
  FROM students
  JOIN register ON students.ids = register.idstu
 WHERE YEAR(data_ora) = 2015
 GROUP BY name, surname;

I think your query order is not correct. The correct query should look alike -

SELECT name, surname, CASE WHEN ROUND(avg(nota),2) >= 9 THEN 'Very good'
                           WHEN ROUND(avg(nota),2) >= 7 THEN 'good' 
                           WHEN ROUND(avg(nota),2) >= 5 THEN 'satisfactorily'
                           ELSE 'Not good' END AS mark
  FROM students
  JOIN register ON students.ids = register.idstu
 WHERE YEAR(data_ora) = 2015
 GROUP BY name, surname;

回复收藏 0 原文

~没有更多了~