使用两个表显示最小平均值和标题的SQL查询
使用两个表显示最小平均值和标题的SQL查询
提问于 2019-02-22 02:06:50
我有两张表：
huffman_students (
id (PK),
fname,
lname,
status ("freshman", "sophomore", "junior", "senior"),
majorcode (FK - huffman_departments.deptcode),
gpa,
admittedDate)
huffman_departments (
deptcode (PK),
deptname,
college)我需要向学校展示最低的平均绩点。
我想出了这个问题：
SELECT MIN(avg_gpa)
FROM
(
SELECT d.college, AVG(s.gpa)
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
);它给出了输出：
MIN(AVG_GPA)
------------
3.436我需要类似如下的输出：
COLLEGE MIN(AVG_GPA)
-----------------------
INF 3.436我尝试了很多不同的查询，但总是得到错误。当我试图向大学展示平均gpa最低的大学时，我得到的一个常见错误是：“不是单一组群函数。”
我正在使用Oracle SQL Plus。
回答 3
Stack Overflow用户
发布于 2019-02-22 02:18:56
您可以将现有查询移动到子查询(或CTE)，然后使用分析函数ROW_NUMBER来过滤相关记录：
WITH cte AS (
SELECT d.college, AVG(s.gpa) avg_spa
FROM
huffman_departments d
INNER JOIN huffman_students s ON s.majorcode = d.deptcode
GROUP BY d.college
)
SELECT *
FROM (
SELECT c.*, ROW_NUMBER() OVER(ORDER BY avg_spa) rn
FROM cte c
) x WHERE rn = 1PS :总是使用显式连接而不是隐式连接(交叉连接+ WHERE子句)。我相应地更改了查询。
Stack Overflow用户
发布于 2019-02-22 02:21:45
您可以使用公用表表达式
with cte0 as
(
SELECT d.college, AVG(s.gpa) agpa
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
)
select * from cte0 c
where c.college (
select ci.college from cte0 ci
group by ci.college
having min(ci.agpa) = a.gpa
)如果您使用的是mysql
SELECT d.college, AVG(s.gpa)
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
order by 2 asc
limit 1Stack Overflow用户
发布于 2019-02-22 03:03:44
使用您已有的资源：
SELECT b.*
FROM (SELECT d.college, AVG(s.gpa) avg_gpa
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
) b
WHERE b.avg_gpa = (SELECT MIN(a.avg_gpa)
FROM
(
SELECT d.college, AVG(s.gpa) avg_gpa
FROM huffman_departments d, huffman_students s
WHERE s.majorcode = d.deptcode
GROUP BY d.college
) a);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/54813543
复制相关文章
相似问题