具有多列的 SQL Server 中的“In”子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35194028/
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
'In' clause in SQL server with multiple columns
提问by Ravi
I have a component that retrieves data from database based on the keys provided. However I want my java application to get all the data for all keys in a single database hit to fasten up things. I can use 'in' clause when I have only one key.
我有一个组件可以根据提供的键从数据库中检索数据。但是，我希望我的 Java 应用程序在单个数据库命中中获取所有键的所有数据以加快速度。当我只有一把钥匙时，我可以使用“in”子句。
While working on more than one key I can use below query in oracle
在处理多个键时，我可以在 oracle 中使用以下查询
SELECT * FROM <table_name>
where (value_type,CODE1) IN (('I','COMM'),('I','CORE'));
which is similar to writing
这类似于写作
SELECT * FROM <table_name>
where value_type = 1 and CODE1 = 'COMM'
and
和
SELECT * FROM <table_name>
where value_type = 1 and CODE1 = 'CORE'
together
一起
However, this concept of using 'in' clause as above is giving below error in 'SQL server'
但是，上述使用“in”子句的概念在“SQL server”中给出了以下错误
ERROR:An expression of non-boolean type specified in a context where a condition is expected, near ','.
ERROR:An expression of non-boolean type specified in a context where a condition is expected, near ','.
Please let know if their is any way to achieve the same in SQL server.
请知道他们是否有任何方法可以在 SQL Server 中实现相同的目标。
采纳答案by Stefan Steinegger
This syntax doesn't exist in SQL Server. Use a combination of Andand Or.
SQL Server 中不存在此语法。使用的组合And和Or。
SELECT *
FROM <table_name>
WHERE
(value_type = 1 and CODE1 = 'COMM')
OR (value_type = 1 and CODE1 = 'CORE')
(In this case, you could make it shorter, because value_type is compared to the same value in both combinations. I just wanted to show the pattern that works like IN in oracle with multiple fields.)
（在这种情况下，您可以缩短它，因为 value_type 在两种组合中都与相同的值进行比较。我只是想展示在具有多个字段的 oracle 中像 IN 一样工作的模式。）
When using IN with a subquery, you need to rephrase it like this:
将 IN 与子查询一起使用时，您需要像这样改写它：
Oracle:
甲骨文：
SELECT *
FROM foo
WHERE
(value_type, CODE1) IN (
SELECT type, code
FROM bar
WHERE <some conditions>)
SQL Server:
SQL 服务器：
SELECT *
FROM foo
WHERE
EXISTS (
SELECT *
FROM bar
WHERE <some conditions>
AND foo.type_code = bar.type
AND foo.CODE1 = bar.code)
There are other ways to do it, depending on the case, like inner joins and the like.
根据情况，还有其他方法可以做到这一点，例如内部联接等。
回答by Andrew
If you have under 1000 tuples you want to check against and you're using SQL Server 2008+, you can use a table values constructor, and perform a join against it. You can only specify up to 1000 rows in a table values constructor, hence the 1000 tuple limitation. Here's how it would look in your situation:
如果您要检查的元组少于 1000 个，并且您使用的是 SQL Server 2008+，则可以使用表值构造函数，并对其执行连接。您最多只能在表值构造函数中指定 1000 行，因此有 1000 个元组限制。以下是它在您的情况下的外观：
SELECT <table_name>.* FROM <table_name>
JOIN ( VALUES
('I', 'COMM'),
('I', 'CORE')
) AS MyTable(a, b) ON a = value_type AND b = CODE1;
This is only a good idea if your list of values is going to be unique, otherwise you'll get duplicate values. I'm not sure how the performance of this compares to using many ANDs and ORs, but the SQL query is at least much cleaner to look at, in my opinion.
如果您的值列表将是唯一的，这只是一个好主意，否则您将获得重复的值。我不确定这与使用许多 AND 和 OR 的性能相比如何，但在我看来，SQL 查询至少更清晰。
You can also write this to use EXIST instead of JOIN. That may have different performance characteristics and it will avoid the problem of producing duplicate results if your values aren't unique. It may be worth trying both EXIST and JOIN on your use case to see what's a better fit. Here's how EXIST would look,
你也可以写这个来使用 EXIST 而不是 JOIN。这可能具有不同的性能特征，如果您的值不唯一，它将避免产生重复结果的问题。可能值得在您的用例上同时尝试 EXIST 和 JOIN 以查看哪个更合适。这是 EXIST 的样子，
SELECT * FROM <table_name>
WHERE EXISTS (
SELECT 1
FROM (
VALUES
('I', 'COMM'),
('I', 'CORE')
) AS MyTable(a, b)
WHERE a = value_type AND b = CODE1
);
In conclusion, I think the best choice is to create a temporary table and query against that. But sometimes that's not possible, e.g. your user lacks the permission to create temporary tables, and then using a table values constructor may be your best choice. Use EXIST or JOIN, depending on which gives you better performance on your database.
总之，我认为最好的选择是创建一个临时表并对其进行查询。但有时这是不可能的，例如您的用户没有创建临时表的权限，然后使用表值构造函数可能是您的最佳选择。使用 EXIST 或 JOIN，具体取决于哪个可以为您提供更好的数据库性能。
回答by Roohi Ali
Normally you can not do it, but can use the following technique.
通常你不能这样做，但可以使用以下技术。
SELECT * FROM <table_name>
where (value_type+'/'+CODE1) IN (('I'+'/'+'COMM'),('I'+'/'+'CORE'));
回答by Alexei
A better solution is to avoid hardcoding your values and put then in a temporary or persistent table:
更好的解决方案是避免对您的值进行硬编码，然后将其放入临时或持久表中：
CREATE TABLE #t (ValueType VARCHAR(16), Code VARCHAR(16))
INSERT INTO #t VALUES ('I','COMM'),('I','CORE')
SELECT DT. *
FROM <table_name> DT
JOIN #t T ON T.ValueType = DT.ValueType AND T.Code = DT.Code
Thus, you avoid storing data in your code (persistent table version) and allow to easily modify the filters (without changing the code).
因此，您可以避免在代码（持久表版本）中存储数据，并允许轻松修改过滤器（无需更改代码）。
回答by JTR
I think you can try this, combine andand orat the same time.
我想你可以试试这个，结合and并or在同一时间。
SELECT
*
FROM
<table_name>
WHERE
value_type = 1
AND (CODE1 = 'COMM' OR CODE1 = 'CORE')
回答by Marcel
What you can do is 'join' the columns as a string, and pass your values also combined as strings.
您可以做的是将列作为字符串“加入”，并将您的值也组合为字符串。
where (cast(column1 as text) ||','|| cast(column2 as text)) in (?1)
The other way is to do multiple ands and ors.
另一种方法是做多个ands和ors。
回答by gladluter
I had a similar problem in MS SQL, but a little different. Maybe it will help somebody in futere, in my case i found this solution (not full code, just example):
我在 MS SQL 中遇到了类似的问题，但有点不同。也许它会帮助某人，在我的情况下，我找到了这个解决方案（不是完整的代码，只是示例）：
SELECT Table1.Campaign
,Table1.Coupon
FROM [CRM].[dbo].[Coupons] AS Table1
INNER JOIN [CRM].[dbo].[Coupons] AS Table2 ON Table1.Campaign = Table2.Campaign AND Table1.Coupon = Table2.Coupon
WHERE Table1.Coupon IN ('0000000001', '0000000002') AND Table2.Campaign IN ('XXX000000001', 'XYX000000001')
Of cource on Coupon and Campaign in table i have index for fast search.
当然，我在表中的 Coupon 和 Campaign 上有快速搜索的索引。
