关于能否对子查询得到的新表a进行子查询
0
悬赏园豆：10
[已解决问题]
解决于 2023-08-02 18:06
-- 查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩
SELECT *
FROM
(
SELECT sc.s_id,s_name,c_name,s_score FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
) a
WHERE a.s_score = -- WHERE后面不能等于聚合函数如where a.s_score = MAX(a.s_score)，所以要子查询
(
SELECT MAX(s_score)
FROM a
)
我想在WHERE a.s_score =后对新表a做子查询，除了让SELECT MAX(s_score)
FROM a的a重新复制粘贴上面inner join那一段，还有什么办法吗？
最佳答案
1
在 MySQL 中，WITH 子句通常被称为 "Common Table Expressions"（CTE），俗称内存临时表，并且是从 MySQL 8.0 版本开始引入的。因此，如果你的 MySQL 数据库版本是 8.0 或更高，则可以使用 WITH 子句进行优化。mssql和oracle的新版本也支持with语句，具体可以百度一下。
以下是在 MySQL 中使用 CTE（WITH 子句）优化的示例：
WITH max_score AS (
SELECT MAX(s_score) AS max_score
FROM (
SELECT sc.s_id, s_name, c_name, s_score
FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
) a
)
SELECT *
FROM (
SELECT sc.s_id, s_name, c_name, s_score
FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
) a
WHERE a.s_score = (SELECT max_score FROM max_score);
请确保你的 MySQL 数据库版本支持 CTE（WITH 子句），并且按照示例中的语法进行使用。
如果你的 MySQL 版本较低，不支持 CTE（WITH 子句），你可以考虑使用临时表或其他查询重写技术来优化查询。
收获园豆：10
感谢解答，如果版本是5.7，有类似的解决方法吗？
其他回答(1)
1
在SQL中，你不能直接在一个子查询中引用另一个子查询的结果。然而，你可以使用通用表表达式 (CTE) 来解决这个问题。CTE 可以让你在查询中创建一个临时的结果集，然后在之后的查询中引用它。
下面是如何使用 CTE 来优化你的查询，避免多次复制粘贴子查询的过程：
sql
Copy code
WITH TeacherCourses AS (
SELECT sc.s_id, s_name, c_name, s_score
FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
)
SELECT *
FROM TeacherCourses a
WHERE a.s_score = (
SELECT MAX(s_score)
FROM TeacherCourses
);
在这个查询中，我们首先将原始的子查询提取到一个 CTE 中，并将其命名为 "TeacherCourses"。然后，在主查询中直接引用这个 CTE，而不是重复子查询的逻辑。这样做不仅使查询更易读，还能优化执行计划，提高查询性能。
请注意，不同的数据库管理系统对 CTE 的支持程度可能有所不同，但在大多数主流数据库中，这种用法应该是有效的。
感谢解答，如果版本是5.7，有类似的解决方法吗？
我想两个回答都给分呢
