请为“in”运算符提供替代方案。 [英] Please provide the alternative for 'in' operator.
问题描述
我在以下查询中使用'in'运算符,它需要花费很多时间来传递输出。我想让它变亮,这样编译和执行的时间可能会少得多。
I am using 'in' operator in the following query, it is taking much time to deliver the output.I want to make it light so that it may take a very less time to compile and execute.
SELECT UserAcc,UserId,count(UserAcc) as cnt from [CPuserTrades] where UserType='L' and [OrderNumber] in (select [OrderNumber] from [CPuserTrades] group by [OrderNumber] having count([OrderNumber])=1) and [FireorClose]='Fire' group by UserId,UserAcc
推荐答案
联接是一个公平的解决方案,但最接近的替代方案是:
A join is a fair solution, but the closest alternative is exists:
SELECT
a.UserAcc,
a.UserId,
count(a.UserAcc) as cnt
from
[CPuserTrades] a
where a.UserType='L'
and exists (
select b.OrderNumber
from [CPuserTrades] b
Where a.[OrderNumber] = b.[OrderNumber] --Note the use of table a in this scope
group by b.[OrderNumber]
having count(b.[OrderNumber])=1
)
and a.[FireorClose]='Fire'
group by
a.UserId,
a.UserAcc
和FYI - 如果你决定使用加入,我也会在这里添加:
and FYI - in case you decide to use a join, I'll add that here too:
SELECT
a.UserAcc,
a.UserId,
count(a.UserAcc) as cnt
from
[CPuserTrades] a
inner join (
select OrderNumber
from [CPuserTrades]
group by [OrderNumber]
having count([OrderNumber])=1) b on a.[OrderNumber] = b.[OrderNumber]
where a.UserType='L'
and a.[FireorClose]='Fire'
group by
a.UserId,
a.UserAcc
更新:响应解决方案#2
您可以将第二个表选择为临时表或表变量,但那 总是一个坏主意。原因如下:
如果您有以下代码:
UPDATE: In response to solution #2
You "could" select the second table into a temporary table or a table variable, but that is always a bad idea. Here's why:
If you have the following code:
Declare @table table
(
OrderNumber int
)
Insert into @table
select [OrderNumber] from [CPuserTrades] group by [OrderNumber] having count([OrderNumber])=1
然后,无论是否使用,查询都必须填写所有可用结果或不。根据桌面大小,它可能会对效率产生非常不利的影响。
有一种替代方案可以以类似的方式工作,但只会选择所需的项目第二个查询。这就是Common Table Expressions(CTE)的工作原理。
看看以下查询:
Then the query will always have to be filled with all available results whether they are used or not. Depending on the table size it could have a very bad impact on efficiency.
There is an alternative that works in a similar way, but will only select the items required by the second query. This is how Common Table Expressions (CTEs) work.
Take a look at the following query:
with myCte as
(
select [OrderNumber] from [CPuserTrades] group by [OrderNumber] having count([OrderNumber])=1
)
-- at this stage, nothing has been selected at all. The CTE is standing in like a query definition waiting for it's use to be defined:
SELECT UserAcc,UserId,count(UserAcc) as cnt
from CPuserTrades CPT
inner join myCte t on CPT.OrderNumber = t.OrderNumber
where CPT.UserType='L'
and [FireorClose]='Fire'
group by UserId,UserAcc
-- only now is the CTE used. It never selects all results, only the ones required for the second query.
为简单起见省略了此选项但它现在根据新帖子相关
I omitted this option for simplicity but it is now relevant in light of new posts
声明表变量
Declare table variable
Declare @table table
(
OrderNumber int
)
Insert into @table
select [OrderNumber] from [CPuserTrades] group by [OrderNumber] having count([OrderNumber])=1
现在,加入此表与您的实际表格和基于订单号过滤它。
Now, Join this table with your actual table and based on order number filter it.
SELECT UserAcc,UserId,count(UserAcc) as cnt
from CPuserTrades CPT,
@table t
where CPT.UserType='L'
and CPT.OrderNumber = t.OrderNumber
and [FireorClose]='Fire'
group by UserId,UserAcc
I没有发短信,请检查一下,我猜它应该有用......
I didn't text this, Please check it once I guess it should work...
这篇关于请为“in”运算符提供替代方案。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!