SQL Server 查询:SELECT 1 WHERE EXISTS 与 SELECT TOP 1 1
我需要提供一个标志 - 如果不满足条件则为 0,如果满足则为 1 - 我可以通过两种不同的方式来实现:
获取员工 ID、姓名,如果子订单中有其他人则为 1 - 如果不满足则为 0:
SELECT e.ID
, e.Name
, ISNULL ( ( SELECT TOP 1 1 FROM Employee se WHERE se.ManagerID = e.ID ) , 0 ) AS HasSubordinates
FROM Employee e
或
SELECT e.ID
, e.Name
, ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM Employee se WHERE se.ManagerID = e.ID ) ) , 0 ) AS HasSubordinates
FROM Employee e
您会选择哪个版本?为什么?
更新1
这个怎么样?
SELECT o.ID
, o.Name
, o.StartDate
, o.EndDate
, ISNULL ( ( SELECT TOP 1 1 FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) , 0 ) AS IsChanged
FROM Orders o
或者
SELECT o.ID
, o.Name
, o.StartDate
, o.EndDate
, ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) ), 0 ) AS IsChanged
FROM Orders o
I need to present a flag - 0 if a condition is not meet, 1 if it is - and I can do it in two different ways :
Get Employee ID, name, 1 if has others in suborder - 0 if not :
SELECT e.ID
, e.Name
, ISNULL ( ( SELECT TOP 1 1 FROM Employee se WHERE se.ManagerID = e.ID ) , 0 ) AS HasSubordinates
FROM Employee e
or
SELECT e.ID
, e.Name
, ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM Employee se WHERE se.ManagerID = e.ID ) ) , 0 ) AS HasSubordinates
FROM Employee e
Which version would you choose and why ?
Update 1
How about this one ?
SELECT o.ID
, o.Name
, o.StartDate
, o.EndDate
, ISNULL ( ( SELECT TOP 1 1 FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) , 0 ) AS IsChanged
FROM Orders o
or
SELECT o.ID
, o.Name
, o.StartDate
, o.EndDate
, ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) ), 0 ) AS IsChanged
FROM Orders o
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我也不会使用:
...因为
SELECT
子句中的相关 SELECTS 很糟糕 - 它们无法扩展,因为它们会针对返回的每一行执行。这意味着您拥有的行越多,相关 SELECT 被调用的次数就越多。Neither, I'd use:
...because correlated SELECTS in the
SELECT
clause are bad - they do not scale, because they execute for every row returned. Which means the more rows you have, the more times the correlated SELECT will be called.我也不会这样做...
原因是(据我所知)当您在 select 语句中有子选择时,该子选择将为返回的每一行执行一次。因此,如果主查询返回 100 行,那么您实际上将运行 101 个查询。当您使用联接时,您只需执行一个需要将左侧和右侧匹配在一起的查询。请注意,如果您在 ManagerId 上有索引,将会有所帮助。
尝试这样的操作:
这与您所得到的略有不同,因为我返回的是总数,而不仅仅是 0|1,但如果您需要 1|0,则很容易更改...
如果这有帮助,请告诉我
I wouldn't do either...
The reason being that (and this is as far as I know) when you have a subselect in the select statement, that subselect will be executed once for every row that is returned. Hence if you had 100 rows returned by the main query you would in effect be running 101 queries. When you use a join you are only doing one query that needs to match the left and the right together. Note it would help if you have an index on ManagerId.
Try something like this:
This is slightly different to what you had as I am returning the total and not just a 0|1 but that is easy to change if you need the 1|0...
Let me know if this helps