在WHERE子句中使用子查询或SELECT语句是否更好/更有效(在MS Access中) [英] Is it better / more efficient to use sub queries or SELECT statements within the WHERE clause (in MS Access)

问题描述

我有一个MS Access数据库，其中包含日期，分数以及与这些分数相关的人员的表，例如

I have an MS Access database that contains a table of dates, scores and the people to whom those scores relate, e.g.

Date   Score Name
1/6/11     5 Dave
1/6/11    10 Sarah
2/6/11     4 Dave
2/6/11     3 Graham
3/6/11     1 Dan
4/6/11    11 Dan
4/6/11     9 Graham

我想写一个查询来查找谁在每个日期的最高得分，即以下结果:

I would like to write a query to find who got the highest socre on each date, i.e. a result of:

Date   Score Name
1/6/11    10 Sarah
2/6/11     4 Dave
3/6/11     1 Dan
4/6/11    11 Dan

我可以想到两种解决此问题的方法(我愿意接受其他建议)，如下所示:

I can think of two solutions to this problem (I am open to alternative suggestions), as follows:

1)编写查询以查找每个日期的最低分数，然后编写第二个查询，将第一个查询连接到原始表.即:

1) Write a query to find the minimum score for each date then a second query joining the first query to the original table. i.e.:

查询1:

SELECT Date, MAX(Score) AS MaxScore FROM ScoresTable GROUP BY Date

Query2:

SELECT ScoresTable.* 
FROM ScoresTable INNER JOIN Query1 
  ON ScoresTable.Date = Query1.Date 
  AND ScoresTable.Score = Query1.MaxScore

[这些可以合并为一个查询:

[These could be combined as a single query:

SELECT ScoresTable.* 
FROM ScoresTable INNER JOIN 
  (SELECT Date, MAX(Score) AS MaxScore 
  FROM ScoresTable GROUP BY Date) Query1 
  ON ScoresTable.Date = Query1.Date 
  AND ScoresTable.Score = Query1.MaxScore

但是我更喜欢将它们分开，以使其他人更容易理解，即他们可以在不了解SQL的情况下使用Access接口.

but I prefer to keep them separate to make it easier for others to follow, i.e. they can use the Access interface without knowing SQL]

2)在WHERE子句中编写一个查询，再进行另一个简单查询(这是我刚刚刚刚读过的新方法，它有名称吗?)，即:

2) Write a single query with another simple query within the WHERE clause (this is a new method I have only just read about, does it have a name?), i.e.:

SELECT * FROM ScoresTable WHERE Score = 
  (SELECT MAX(Score) FROM ScoresTable AS st WHERE st.Date = ScoresTable.Date)

后者显然更优雅，但运行起来似乎更慢.哪个更好的选择?数据集可能会变得很大.

The latter is clearly more elegant, but appears to run more slowly. Which is the better option? The datasets can get quite large.

推荐答案

您的单个​​复合查询对我而言似乎是最佳的，我怀疑您可以做得更简单或更有效.
明智地使用表中的索引应确保查询运行得非常快.

Your single composite query already looks optimal to me, I doubt that you can do simpler or more efficient.
Judicious use of indexes in your table should ensure that the query runs pretty fast.

您的上一个查询称为相关子查询.
它有时是有用的，但可能非常慢:需要对ScoresTable中的每个记录执行子查询，因为子查询的结果取决于ScoresTable中每个记录的值.
对于数据库引擎而言，这是相当困难的.

Your last query is called a Correlated subquery.
It is sometimes useful, but can be very slow: the subquery will need to be executed for each record in the ScoresTable because the result of the subquery depends on the value of each individual record in ScoresTable.
This is rather difficult for the database engine to optimise.

如果您有兴趣查找有关查询计划程序如何优化查询的详细信息，请查看以下文章，它们将向您展示幕后的故事:

If you are interested in finding out details about how the query planner optimises your queries, have a look at these articles, they'll show you what's under the hood:

• 使用Microsoft Jet的ShowPlan编写更有效的查询
• Access 2002桌面开发人员手册，第15章:应用程序优化

• Use Microsoft Jet's ShowPlan to write more efficient queries
• Access 2002 Desktop Developer's Handbook, Chapter 15: Application Optimization

这篇关于在WHERE子句中使用子查询或SELECT语句是否更好/更有效(在MS Access中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！