在WHERE子句中使用SELECT语句

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

在 WHERE 子句中使用 SELECT 语句的行为有一个专门的术语吗?这个做法好还是不好?

这是否有更好的替代方案?

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

这个版本虽然不太优雅,但似乎比我的以前的版本运行更快。我不喜欢它,因为在GUI中显示得不太清晰(而且需要SQL初学者理解)。我可以将其拆分为两个单独的查询,但这样会使事情变得混乱...

注:我需要的不仅仅是日期和分数(例如名称)。


你假设他的数据库实现了这个功能。而且,窗口函数几乎肯定不是必需的,以满足Jo的需求。 - BonyT
确实,在这个简单的例子中它们并不是必需的,但是它们为这些类型的查询的更复杂的变体提供了优雅的解决方案,所以值得了解。 - Winston Smith
我同意 - 值得了解 - 对于显然很新的SQL初学者来说,仍有很多需要掌握的地方。 - BonyT
1
你的EDIT是一个更好的解决方案,我认为更加优雅。正如你已经注意到的那样,它也更加高效。它可能看起来更复杂,但初学者应该从一开始就学会编写良好的代码,而不是先养成坏习惯。 - Winston Smith
7个回答

9

这被称为相关子查询。它有其用途。


1
谢谢@Mladen。在您的看法中,这是问题的最佳/最有效解决方案吗? - jofitz
5
不是这样的。按照你的方式,子查询会为表中的每一行单独运行。像你所做的那样使用连接总是更好的选择。 - Mladen Prajdic

8

这并不是一个不好的实践。它们通常被称为SUBQUERYSUBSELECTNESTED QUERY

尽管这是一项相对昂贵的操作,但在处理数据库时遇到许多子查询是非常常见的,因为这是执行某些类型数据操作的唯一方式。


1
@Gunner:是的。还有子查询。 - Pablo Santa Cruz
通常可以将它们因式分解为JOIN,但是现在SQL服务器非常擅长自行确定最佳查询计划。尽管如此,编写最佳代码而不是依赖编译器优化也无妨。 - Winston Smith
True。你通常可以将它们因式分解成一个连接。但是有一些操作是你无法使用JOIN执行的。例如,ANY/SOME/ALL子查询操作... - Pablo Santa Cruz
@WinstonSmith:我能把我的例子因式分解成一个JOIN吗(或者这就是我在编辑中所做的?);恐怕我对精确术语的了解非常有限。 - jofitz
@Pablo:从查看其他人的评论来看,我拥有的是一个_相关子查询_。我非常熟悉简单子查询,但这是我第一次遇到相关子查询。你能建议更好的方法吗? - jofitz

3

有一种更好的方法可以实现您想要的结果,使用 SQL Server 的分析(或窗口)函数

SELECT DISTINCT Date, MAX(Score) OVER(PARTITION BY Date) FROM ScoresTable

如果您需要的不仅仅是日期和最高分数的组合,您可以使用排名函数,例如:
SELECT  *
FROM    ScoresTable t
JOIN (   
    SELECT 
        ScoreId,
        ROW_NUMBER() OVER (PARTITION BY Date ORDER BY Score DESC) AS [Rank] 
        FROM ScoresTable
) window ON window.ScoreId = p.ScoreId AND window.[Rank] = 1

如果您希望在它们共享相同的MAX(Score)时返回多个记录,则可以使用RANK()而不是ROW_NUMBER()


谢谢@WinstonSmith,但是你的建议似乎比必要的更复杂。这个方案比我的初始建议更好吗? - jofitz
无论如何,它在Access上都不起作用,但如果您将来转向SQL服务器,则需要记住这一点。 - Winston Smith

2

这是一个相关子查询。

(这是一个“嵌套”查询 - 虽然这个术语非常非技术性)

内部查询从外部查询中获取值(WHERE st.Date = ScoresTable.Date),因此对于外部查询中的每一行,它都会被评估一次。

也有一个非相关形式,在这种情况下,内部查询是独立的,因此只执行一次。

例如:

 SELECT * FROM ScoresTable WHERE Score = 
   (SELECT MAX(Score) FROM Scores)

使用子查询并没有什么问题,除非在不需要的情况下 :)

根据你在select语句中所需的列,你的语句可以重写为聚合函数。

SELECT Max(score), Date FROM ScoresTable 
Group By Date

如果你需要的不仅仅是MAX(Score)和Date对,聚合函数就无法工作。 - Winston Smith
你假设他需要更多 - 最好的实践是只编写满足要求的代码,而不是引入不必要的复杂性。 - BonyT
我并不是在假设——从查询语句SELECT *就可以明显看出来。当然,这也可能只是懒惰,但那同样也是一种假设! - Winston Smith
现在我感觉更开心了,我已经取消了我的踩票! - Winston Smith
正如在其他地方所评论的那样,@WinstonSmith是正确的,我需要的不仅仅是得分和日期。 - jofitz

2

子查询的原则并不坏,但我认为您不应该在您的例子中使用它。如果我理解正确,您想要获取每个日期的最高分数。在这种情况下,您应该使用GROUP BY。


由于他选择了*,看起来他想要的不仅仅是分数和日期的组合。也许他需要弄清楚哪些学生得到了这些分数。 - Winston Smith
你说得对。幸运的是,在MySQL中(虽然我不确定他是否在使用),他将能够选择未聚合或未在group by子句中的列。 - user254875486
你说得一点没错,@WinstonSmith,我需要获取与分数相关联的姓名,所以简单的GROUP BY是不够的。不幸的是,这次我没有使用MySQL,@Lex;我很惭愧地说我在使用Access :) - jofitz

1
在您的情况下,为什么不使用GROUP BY和HAVING子句来代替将表连接到自身。您还可以使用其他有用的函数。请查看此链接

GROUP BY和HAVING在这种情况下不起作用,因为正如原问题所述:“我需要的不仅仅是日期和分数(例如姓名)”。如果您再次检查,您会发现别名已经在问题中使用了。 - jofitz

0

子查询是它的名称。

有时需要使用,但好坏取决于如何应用。


网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接