使用单一且快速的 SQL 查询获取数据 [英] Fetch data with single and fast SQL query

问题描述

我有以下数据:

ExamEntry   Student_ID     Grade
  11           1             80
  12           2             70
  13           3             20
  14           3             68
  15           4             75

我想找到所有通过考试的学生.在这种情况下，如果考试很少那个学生参加了，我需要找到最后的结果.

I want to find all the students that passed an exam. In this case, if there are few exams that one student attended to, I need to find the last result.

所以，在这种情况下，我认为所有学生都通过了.

So, in this case I'd get that all students passed.

我可以通过一个快速查询找到它吗?我是这样做的:

Can I find it with one fast query? I do it this way:

1. 通过以下方式查找条目列表从 Student_ID 的数据组中选择 max(ExamEntry)

1. Find the list of entries by select max(ExamEntry) from data group by Student_ID

查找结果:

从数据中选择 ExamEntry，其中 ExamEntry 在 ( ) 中.

select ExamEntry from data where ExamEntry in ( ).

但这非常慢 - 我得到了大约 1000 个条目，这个 2 步过程需要 10 秒.

But this is VERY slow - I get around 1000 entries, and this 2 step process takes 10 seconds.

有没有更好的方法?

谢谢.

推荐答案

如果您的查询在您的表中有 1000 条记录时非常缓慢，则说明有问题.对于现代数据库系统，一个包含 1000 个条目的表被认为非常小.
最有可能的是，您没有为您的表提供(主)键?

If your query is very slow at with 1000 records in your table, there is something wrong. For a modern Database system a table containing, 1000 entries is considered very very small.
Most likely, you did not provid a (primary) key for your table?

假设如果至少有一个成绩高于所需的最低成绩，学生就会通过，则适当的查询是:

Assuming that a student would pass if at least on of the grades is above the minimum needed, the appropriate query would be:

SELECT 
  Student_ID
, MAX(Grade) AS maxGrade
FROM table_name
GROUP BY Student_ID
HAVING maxGrade > MINIMUM_GRADE_NEEDED

如果您确实需要最新成绩高于最低成绩:

If you really need the latest grade to be above the minimum:

SELECT 
  Student_ID
, Grade
FROM table_name
WHERE ExamEntry IN ( 
    SELECT 
      MAX(ExamEntry) 
    FROM table_name 
    GROUP BY Student_ID
)
HAVING Grade > MINIMUM_GRADE_NEEDED

这篇关于使用单一且快速的 SQL 查询获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！