SQL 我应该在子查询中使用 Top(1)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/496070/
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
Should I use Top(1) in a SubQuery
提问by pfunk
Example Query:
示例查询：
select *
from A join B on A.ID = B.SOMEVALUE
where A.VALUE="something" and
B.ID =
(select ID from B where SOMEVALUE = A.ID and
THISDATE = (select max(SOMEDATE) from B where ...))
so, if you can read SQL you should see that I am doing a couple correlated subqueries to narrow down the results of the join . (and yes, this is horribly over-simplified).
因此，如果您可以阅读 SQL，您应该会看到我正在执行几个相关的子查询来缩小 join 的结果。（是的，这过于简单化了）。
In certain cases the subquery:
在某些情况下，子查询：
select ID from B where SOMEVALUE = A.ID and
THISDATE = (select max(SOMEDATE) from B where ...)
can return more than 1 value, which causes an error
可以返回 1 个以上的值，这会导致错误
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
“子查询返回的值超过 1。当子查询跟随 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的。”
which I fully expect. This is obviously not a good thing and I have code in place to (hopefully) prevent these duplicates from getting into the database in the first place (ie table B shouldonly have 1 row that matches the
我完全期待。这显然不是一件好事，我有代码来（希望）首先防止这些重复项进入数据库（即表 B应该只有 1 行与
SOMEVALUE = A.ID and max(SOMEDATE)
criteria), however end-users are nothing if not creative in finding ways I can't think of to break software.
标准），但是最终用户在寻找我无法想到的破坏软件的方法方面没有创造力。
So now to my question:
所以现在我的问题：
Would it be better to change the first subquery to
将第一个子查询更改为
select top 1 * from B ...
to prevent the user from seeing an error when/if (hopefully never) this situation arises or let the error come through. I'm leaning to not adding the top statement and letting the error come through rather then let the user see potentially incorrect data. I'm wondering if anyone has any thoughts on Best Practices in a situation like this...
防止用户在/如果（希望永远不会）出现这种情况或让错误发生时看到错误。我倾向于不添加 top 语句并让错误通过，而不是让用户看到可能不正确的数据。我想知道在这种情况下是否有人对最佳实践有任何想法......
回答by John
Normally TOP 1 is a good idea.
通常TOP 1是个好主意。
Consider a large table with millions of rows with no index on the column you are matching, however you are only looking for a single row.
考虑一个包含数百万行的大表，在您匹配的列上没有索引，但您只查找一行。
SELECT TOP 1 will mean the table scan stops as soon as the one item is found.
SELECT TOP 1 意味着只要找到一项就停止表扫描。
Without the TOP 1, the table scan will continue right through to the end.
如果没有 TOP 1，表扫描将一直持续到最后。
As with anything that involves scanning (or brute force) to do the search. Using TOP 1, it should on average be 50% quicker than not using TOP 1.
与涉及扫描（或蛮力）进行搜索的任何事情一样。使用 TOP 1，它平均应该比不使用 TOP 1 快 50%。
However, Depending on what you need to return back, A real performance gain can normally be made by using EXISTS.
但是，根据您需要返回的内容，使用 EXISTS 通常可以实现真正的性能提升。
Instead of writing
而不是写作
SELECT * FROM table t
WHERE t.id = (SELECT TOP 1 foreignid from table2)
You can use
您可以使用
SELECT * FROM table t
WHERE EXISTS (SELECT 1 from table2 WHERE foreignid = t.id)
回答by bobwienholt
Why are you joining table A and B... then selecting from B in the sub query... and comparing to a column in A???
为什么要加入表 A 和 B...然后在子查询中从 B 中选择...并与 A 中的列进行比较？
Wouldn't this be equivalent:
这不是等价的吗：
select *
from A join B on A.ID = B.SOMEVALUE
where A.VALUE="something" and
THISDATE = (select max(SOMEDATE) from B where ...))
Also, if you are expecting to get one row total from this entire query... wouldn't this work:
此外，如果您希望从整个查询中总共获得一行……这行不通：
select top 1 *
from A join B on A.ID = B.SOMEVALUE
where A.VALUE="something"
Order by B.SOMEDATE DESC
回答by Amy B
Coding Practice: Placing top1 in a subquery which is required to return a single value.
编码实践：将 top1 放在需要返回单个值的子查询中。
Pros:
优点：
- Allows execution to continue.
- Allows the query to ignore insigificant Extra Values.
- Stops looking for new values when the first Value has been found (performant).
- 允许继续执行。
- 允许查询忽略无关紧要的额外值。
- 找到第一个值后停止寻找新值（性能良好）。
Cons:
缺点：
- Prevents the query from complaining about significant Extra Values.
- If order is not specified in the query, there is no control over which element top1 will select. This can cause a different result when the query is executed a second time.
- 防止查询抱怨重要的额外值。
- 如果查询中未指定 order，则无法控制 top1 将选择哪个元素。当第二次执行查询时，这可能会导致不同的结果。
回答by Michael Haren
I'd recommend the TOP 1 approach. It will probably help performance (not likely to hurt it).
我推荐TOP 1方法。它可能会帮助性能（不太可能伤害它）。
The notion that without it you'll catch errors is honorable but a little misplaced. If an error occurs here months from now, it will NOT be intuitive at all why it occurred or what's going on. Instead, I'd focus on enforcing data integrity elsewhere.
没有它你会发现错误的想法是光荣的，但有点放错了地方。如果几个月后这里发生错误，那么它发生的原因或发生的事情就完全不直观了。相反，我会专注于在其他地方强制执行数据完整性。
回答by inkedmn
Why not use LIMIT 1at the end of your sub-select?
为什么不在LIMIT 1子选择的末尾使用？
回答by Kezzer
If you're looking to return just a single row you could do one of two things. The first is to change your equality check to instead check containment like so
如果你只想返回一行，你可以做两件事之一。第一个是改变你的平等检查，而不是像这样检查遏制
select ID from B where SOMEVALUE = A.ID and
THISDATE IN (select max(SOMEDATE) from B where ...)
(Notice the IN)
（注意IN）
Secondly you could do
其次你可以做
select TOP 1 ID from B where SOMEVALUE = A.ID and
THISDATE IN (select max(SOMEDATE) from B where ...)
if you're only looking for one value only.
如果您只寻找一个值。
Or as you said, you could change the sub-query to SELECT TOP 1 which is okay, in my opinion, because as long as the WHERE clause isn't dependant on the outer-query it will probably execute the nested query only once and rely on a statically stored value from there on out like so
或者如您所说，您可以将子查询更改为 SELECT TOP 1，这在我看来是可以的，因为只要 WHERE 子句不依赖于外部查询，它就可能只执行一次嵌套查询，并且像这样依赖静态存储的值
select ID from B where SOMEVALUE = A.ID and
THISDATE = (select TOP 1 * from B where ...)
So there's a few options, but I'm not entirely sure of their efficiency.
所以有几个选择，但我不完全确定它们的效率。
