SQL 在 WHERE 子句中使用 SELECT 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6424301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a SELECT statement within a WHERE clause
提问by jofitz
SELECT * FROM ScoresTable WHERE Score =
(SELECT MAX(Score) FROM ScoresTable AS st WHERE st.Date = ScoresTable.Date)
Is there a name to describe using a SELECT statement within a WHERE clause? Is this good/bad practice?
是否有名称可以在 WHERE 子句中使用 SELECT 语句进行描述？这是好/坏的做法吗？
Would this be a better alternative?
这会是一个更好的选择吗？
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
It is far less elegant, but appears to run more quickly than my previous version. I dislike it because it is not displayed very clearly in the GUI (and it needs to be understood by SQL beginners). I could split it into two separate queries, but then things begin to get cluttered...
它远没有那么优雅，但似乎比我以前的版本运行得更快。我不喜欢它，因为它在 GUI 中显示的不是很清楚（需要 SQL 初学者理解）。我可以将它拆分为两个单独的查询，但随后事情开始变得混乱......
N.B. I need more than just Date and Score (e.g. name)
注意我需要的不仅仅是日期和分数（例如姓名）
采纳答案by Mladen Prajdic
It's called correlated subquery. It has it's uses.
它被称为相关子查询。它有它的用途。
回答by Pablo Santa Cruz
It's not bad practice at all. They are usually referred as SUBQUERY, SUBSELECTor NESTED QUERY.
这根本不是坏习惯。它们通常被称为SUBQUERY、SUBSELECT或NESTED QUERY。
It's a relatively expensive operation, but it's quite common to encounter a lot of subqueries when dealing with databases since it's the only way to perform certain kind of operations on data.
这是一个相对昂贵的操作，但是在处理数据库时遇到很多子查询是很常见的，因为它是对数据执行某种操作的唯一方法。
回答by Winston Smith
There's a much better way to achieve your desired result, using SQL Server's analytic (or windowing) functions.
使用 SQL Server 的分析（或窗口）函数，有一种更好的方法可以实现您想要的结果。
SELECT DISTINCT Date, MAX(Score) OVER(PARTITION BY Date) FROM ScoresTable
If you need more than just the date and max score combinations, you can use ranking functions, eg:
如果您需要的不仅仅是日期和最大分数组合，您可以使用排名函数，例如：
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
You may want to use RANK()instead of ROW_NUMBER()if you want multiple records to be returned if they both share the same MAX(Score).
如果您希望返回多个记录共享相同的 MAX(Score)，您可能需要使用RANK()而不是ROW_NUMBER()。
回答by rael_kid
The principle of subqueries is not at all bad, but I don't think that you should use it in your example. If I understand correctly you want to get the maximum score for each date. In this case you should use a GROUP BY.
子查询的原则一点也不差，但我认为你不应该在你的例子中使用它。如果我理解正确，您想获得每个日期的最高分。在这种情况下，您应该使用 GROUP BY。
回答by BonyT
This is a correlated sub-query.
这是一个相关的子查询。
(It is a "nested" query - this is very non-technical term though)
（这是一个“嵌套”查询——不过这是一个非常非技术性的术语）
The inner query takes values from the outer-query (WHERE st.Date = ScoresTable.Date) thus it is evaluated once for each row in the outer query.
内部查询从外部查询（WHERE st.Date = ScoresTable.Date）中获取值，因此它对外部查询中的每一行进行一次评估。
There is also a non-correlated form in which the inner query is independent as as such is only executed once.
还有一种非相关形式，其中内部查询是独立的，因此只执行一次。
e.g.
例如
SELECT * FROM ScoresTable WHERE Score =
(SELECT MAX(Score) FROM Scores)
There is nothing wrong with using subqueries, except where they are not needed :)
使用子查询没有任何问题，除非不需要它们:)
Your statement may be rewritable as an aggregate function depending on what columns you require in your select statement.
根据您在 select 语句中需要的列，您的语句可能可重写为聚合函数。
SELECT Max(score), Date FROM ScoresTable
Group By Date
回答by dr.Crow
In your case scenario, Why not use GROUP BY and HAVING clause instead of JOINING table to itself. You may also use other useful function. see this link
在您的情况下，为什么不使用 GROUP BY 和 HAVING 子句而不是 JOINING 表本身。您还可以使用其他有用的功能。看到这个链接
回答by Shamim Hafiz
Subquery is the name.
子查询是名称。
At times it's required, but good/bad depends on how it's applied.
有时它是必需的，但好/坏取决于它的应用方式。
