请为“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屋！