我有一个MS Access数据库,其中包含日期、分数和与这些分数相关的人的表格,例如
日期分数名称2011年1月6日5 Dave2011年1月6日10 Sarah2011年2月6日4 Dave2011年2月6日3 Graham2011年3月6日1丹2011年4月6日Dan2011年4月6日9 Graham
我想写一个查询,找出谁在每个日期获得了最高的分数,即的结果
日期分数名称2011年1月6日10 Sarah2011年2月6日4 Dave2011年3月6日1丹2011年4月6日Dan
我可以想出两种解决这个问题的方法(我愿意接受其他建议),如下所示:
1) 编写一个查询来查找每个日期的最低分数,然后编写第二个查询,将第一个查询连接到原始表。即:
查询1:
SELECT Date, MAX(Score) AS MaxScore FROM ScoresTable GROUP BY Date
查询2:
SELECT ScoresTable.*
FROM ScoresTable INNER JOIN Query1
ON ScoresTable.Date = Query1.Date
AND ScoresTable.Score = Query1.MaxScore
[这些可以合并为一个查询:
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接口
2) 在WHERE子句中用另一个简单的查询编写一个查询(这是我刚刚读到的一个新方法,它有名字吗?),即:
SELECT * FROM ScoresTable WHERE Score =
(SELECT MAX(Score) FROM ScoresTable AS st WHERE st.Date = ScoresTable.Date)
后者显然更优雅,但似乎运行得更慢。哪个是更好的选择?数据集可能会变得相当大。
您的单个复合查询对我来说已经是最优的了,我怀疑您是否可以做得更简单或更高效
明智地使用表中的索引应该可以确保查询运行得非常快。
您的最后一个查询称为Correlated子查询
它有时很有用,但速度可能很慢:需要对ScoresTable中的每个记录执行子查询,因为子查询的结果取决于ScoresTable中每个单独记录的值
这对于数据库引擎来说相当难以优化。
如果你有兴趣了解查询计划器如何优化你的查询的细节,看看这些文章,它们会向你展示背后的东西:
- 使用Microsoft Jet的ShowPlan编写更高效的查询
- Access 2002桌面开发人员手册,第15章:应用程序优化