mysql - SQL - 如何为参数提供 "AS"值

Question

所以我有两个表；

course 包含列 id, description, units, 和 price_per_unit。

student 具有 id、name、 和 course_id 列。

我想SELECT总学费最高的学生(units * price_per_unit)

我试过这个 SELECT STATEMENT;

SELECT student.name, 
       course.description, 
       course.units * course.price_per_unit AS "total_tuition_fee" 
FROM student 
LEFT JOIN course 
ON course.total_tuition_fee = (SELECT MAX(course.total_tuition_fee) FROM course);

但它不起作用，我使用 AS 作为我的 course.units * course.price_per_unit 并将其命名为 total_tuition_fee 但我不能把它放在我的 ON 参数中。

Answer 1

您的查询有点困惑。首先需要匹配join权，然后按学费降序排列。

SELECT student.name, 
   course.description, 
   course.units * course.price_per_unit AS "total_tuition_fee" 
FROM student 
INNER JOIN course 
  ON course.id = student.course_id
ORDER BY (course.units * course.price_per_unit) DESC
LIMIT 1;

这将选择总学费最高的学生类(class)组合。 LIMIT 1 选择表中的第一个条目，该条目按单位和单位价格的乘积排序。

Note: This could be slower than usual as the calculation is done twice. So you could create a subselect with all fees and then select the one with the highest fee:

SELECT x.student_name, x.course_description, x.total_tuition_fee 
FROM
(SELECT student.name AS student_name, 
   course.description AS course_description, 
   course.units * course.price_per_unit AS "total_tuition_fee" 
FROM student 
INNER JOIN course 
  ON course.id = student.course_id) x
ORDER BY x.total_tuition_fee DESC
LIMIT 1;