SQL - 如何找到成绩最高的人 [英] SQL - How to find the person with the highest grade

问题描述

我正在尝试查找在大数据"课程中获得最高分的人的姓名.

I am trying to find the name of the person who received the highest grade in the "Big Data" course.

我有 3 个不同的表:

I have 3 different tables:

People (id, name, age, address)
---------------------------------------------------
p1  | Tom Martin| 24 | 11, Integer Avenue, Fractions, MA
p2  | Al Smith  | 33 | 26, Main Street, Noman's Land, PA
p3  | Kim Burton| 40 | 45, Elm Street, Blacksburg, VA
---------------------------------------------------

Courses (cid, name, department)
---------------------------------------------------------
c1 | Systematic Torture | MATH
c2 | Pretty Painful     | CS
c3 | Not so Bad         | MATH
c4 | Big Data           | CS
---------------------------------------------------------

Grades (pid, cid, grade)
---------------------------------------------------
p1  |  c1       | 3.5 
p2  |  c3       | 2.5
p3  |  c2       | 4.0
p3  |  c4       | 3.85
---------------------------------------------------

我不知道如何在不使用任何花哨的 SQL 功能的情况下找到成绩最高的人.也就是说，我只想使用 SELECT、FROM、WHERE、UNION、INTERSECT、EXCEPT、CREATE VIEW 和算术比较运算符，如 =、<、>.我的结果展示了我试图实现的目标之外的东西.

I can't figure out how to find the person with the highest grade without using any fancy SQL feature. That is, I just want to use SELECT, FROM, WHERE, UNION, INTERSECT, EXCEPT, CREATE VIEW and arithmetic comparison operators like =, <, >. My outcome is showing something other than what I try to achieve.

这是我迄今为止尝试过的:

This is what I have tried so far:

    CREATE VIEW TEMPFIVE AS 
    SELECT G1.pid FROM Grades AS G1, Grades AS G2 WHERE G1.pid = G2.pid AND G1.cid = G2.cid

  SELECT People.name, Courses.name FROM TEMPFIVE, People, Courses WHERE TEMPFIVE.pid = People.pid AND Courses.name = "Big Data";

+------------+----------+
| name       | name     |
+------------+----------+
| Tom Martin | Big Data |
| Al Smith   | Big Data |
|Kim Burton  | Big Data |
|Kim Burton  | Big Data |
+------------+----------+

推荐答案

最简单的方法是将 LIMT 1 与 ORDER BY DESC 子句一起使用:

The easiest way is to use LIMT 1 with an ORDER BY DESC clause:

SELECT p.name, c.name, g.grade 
FROM People AS p
JOIN Grades AS g ON p.id = g.pid
JOIN Courses AS c ON c.cid = g.cid
WHERE c.name = "Big Data"
ORDER BY g.grade DESC LIMIT 1

这篇关于SQL - 如何找到成绩最高的人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！