SQL变量与IN之间的IN运算符的替代方案 [英] SQL Alternative to IN operator with variable and between

问题描述

我正在尝试将一些变量放入IN运算符，但不幸的是它无法正常工作. (在互联网上找到有关它的一些文章).那我该怎么办?

I'm trying to put some variables to an IN Operator but unfortunately it's not working. (found some articles on the internet about it). So what can I do?

我有一些堆积在一起的查询.但是然后我有几次相同的论点，那没问题.但是每次我要进行新搜索时，我都必须更改所有参数.因此，为了简便起见，我在查询的顶部使用了变量，因此我什至都不会忘记要更改的变量.除IN运算符外，这也起作用.除了使用Dynamic SQL之外，您不能以某种方式将多个变量与IN运算符一起使用.

I have some stacked queries combined to one. But then I have several times the same argument, well that's no problem. But I have to change all the arguments every times I want to make a new search. So to make it easy I used variables at the top of the query so I can't forget even one to change. And that's working also except for the IN operator. Somehow you can't use multiple variables with the IN operator except when using Dynamic SQL.

现在我有

where [Item No_] not in ('0000900','00009900','00009906')

排除我不想看到的产品.目前只有3个，但必须更多一些，一次大约20个，有些在00005100-00007500之类的范围内(是2400). (在下一个查询中，我只需要那些2400)

to exclude the products I don't want to see. At this moment there are only 3 but it have to be more, about 20 single once and some are in a range like 00005100 - 00007500 (yes that are 2400). (and in the next query I just need those 2400)

所以我尝试了Declare @Exception，但这不起作用.现在，我想知道是否有一种方法可以使其工作.如果不带变量，则至少是IN和between语句的组合.或其他方法.

So I tried to Declare @Exception, but that's not working. Now I wonder if there is a way to make it work. If not with a variable then at least a combination of a IN and between statement. Or some other way around.

这是整个查询.我注释了@Exception变量:

And here is the whole query. I commented out the @Exception variable:

Declare @Start datetime = '01.04.2015',
        @Ende datetime = '30.04.2015';
/*      @Ausnahme ;
Insert @Ausnahme values (00009000),(00009900),(00009906);
*/

SELECT t1.[Location Code], CAST(t1.Umsatz as DECIMAL(18,2))as Umsatz , CAST(COALESCE(t2.Ersatznachweis, 0) as DECIMAL(18,2)) as Ersatznachweis, CAST(T5.Rückgabe as Decimal(18,2)) as Rückgabe
FROM (
SELECT [Location Code],  SUM(WareBrutto) AS Umsatz
FROM (SELECT  DISTINCT [Location Code], [Document No_] , WareBrutto from [Item Ledger Entry] 
      WHERE [Location Code] between '0000' and '0040' and [Document Date] between @Start and @Ende) t
GROUP BY [Location Code]) as t1

LEFT JOIN
(select [Location Code], sum([Quantity]*Bruttopreis) as Ersatznachweis  from [Item Ledger Entry] 
where [Location Code] between '0000' and '0040' and [Item No_] not IN ('00009000','00009900','00009906') and Gutschrift = '1' and [Document Date] between @Start and @Ende
Group By [Location Code]) as t2 

on t1.[Location Code] = t2.[Location Code] 

Left JOIN
(Select [Location Code], sum(Quantity*PR_Brutto*-1) As Rückgabe
From ( 
select [Location Code], [Item No_], [Quantity] from [Item Ledger Entry] 
where [Location Code] between '0000' and '0040' and [Item No_] not in ('00009000','00009900','00009906') and Rückgabe = '1' and [Document Date] between @Start and @Ende ) as T3

Left Join 
(Select ARTNR, PR_Brutto from dep_prs
where LIdx = '4' and Preisgruppe = '1') as T4 ON T3.[Item No_] = T4.ARTNR
Group By T3.[Location Code]) as T5 on t1.[Location Code] = T5.[Location Code] 

Order by t1.[Location Code]

推荐答案

您可以声明表变量(或参数，如果它是过程或函数的一部分，则可以使用该变量)并将其用于not in部分:

You can declare a table variable (or parameter if it's a part of a procedure or function) and use it for the not in part:

DECLARE @NotIn table (
    NotInValues int
)

INSERT INTO @NotIn Values
('00009000'),
('00009900'),
('00009906')

并像下面这样在您的代码中使用它:

and use it in your code like this:

where [Location Code] between '0000' and '0040' 
and [Item No_] not IN (select NotInValues from @NotIn) 
and Gutschrift = '1' 
and [Document Date] between @Start and @Ende    

注释#1::对于大量值，不存在可能会比不存在表现更好

Note #1: for a large number of values, not exists will probably perform better then not in

注释#2::如果它是存储过程的一部分，则需要创建用户定义的表类型，并使用它来声明表值参数.此外，表值参数是只读的，因此对它们执行DML语句(插入/更新/删除)将引发错误.

Note #2: If it's a part of a stored procedure you will need to create a user defined table type, and use it to declare the table valued parameter. Also, table valued parameters are readonly, so performing DML statements (insert/update/delete) on them will raise an error.

要创建udt:

CREATE TYPE IntegerList As Table
(
    IntValue int
)

要在存储过程参数列表中声明它:

To declare it in the stored procedure parameters list:

CREATE PROCEDURE procedureName
(
   @IntList dbo.IntegerList READONLY
   -- Note that the readonly must be a part of the parameter declaration.
)

这篇关于SQL变量与IN之间的IN运算符的替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！