查找多个已知 ID 项的替代 SQL 方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/768436/
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
Alternative SQL ways of looking up multiple items of known IDs?
提问by AlexanderJohannesen
Is there a better solution to the problem of looking up multiple known IDs in a table:
在一个表中查找多个已知ID的问题有没有更好的解决方案：
SELECT * FROM some_table WHERE id='1001' OR id='2002' OR id='3003' OR ...
I can have several hundreds of known items. Ideas?
我可以拥有数百个已知项目。想法？
回答by Eoin Campbell
SELECT * FROM some_table WHERE ID IN ('1001', '1002', '1003')
and if your known IDs are coming from another table
如果您的已知 ID 来自另一个表
SELECT * FROM some_table WHERE ID IN (
SELECT KnownID FROM some_other_table WHERE someCondition
)
回答by Joel Coehoorn
The first (naive) option:
第一个（天真）选项：
SELECT * FROM some_table WHERE id IN ('1001', '2002', '3003' ... )
However, we should be able to do better. INis very bad when you have a lot of items, and you mentioned hundreds of these ids. What creates them? Where do they come from? Can you write a query that returns this list? If so:
但是，我们应该能够做得更好。 IN当你有很多项目并且你提到了数百个这样的 id 时，这是非常糟糕的。是什么创造了它们？他们来自哪里？你能写一个返回这个列表的查询吗？如果是这样的话：
SELECT *
FROM some_table
INNER JOIN ( your query here) filter ON some_table.id=filter.id
回答by SQLMenace
回答by Vincent Buck
ORs are notoriously slow in SQL.
OR 在 SQL 中是出了名的慢。
Your question is short on specifics, but depending on your requirements and constraints I would build a look-up table with your IDs and use the EXISTS predicate:
您的问题没有具体说明，但根据您的要求和约束，我将使用您的 ID 构建一个查找表并使用 EXISTS 谓词：
select t.id from some_table t
where EXISTS (select * from lookup_table l where t.id = l.id)
回答by Quassnoi
In Oracle, I always put the id's into a TEMPORARY TABLEto perform massive SELECT's and DMLoperations:
在 中Oracle，我总是将id's 放入 a 中TEMPORARY TABLE以执行大规模的SELECT's 和DML操作：
CREATE GLOBAL TEMPORARY TABLE t_temp (id INT)
SELECT *
FROM mytable
WHERE mytable.id IN
(
SELECT id
FROM t_temp
)
You can fill the temporary table in a single client-server roundtrip using Oraclecollection types.
您可以使用Oracle集合类型在单个客户端-服务器往返中填充临时表。
回答by MatBailie
We have a similar issue in an application written for MS SQL Server 7. Although I dislike the solution used, we're not aware of anything better...
我们在为 MS SQL Server 7 编写的应用程序中遇到了类似的问题。虽然我不喜欢使用的解决方案，但我们不知道有什么更好的......
'Better' solutions exist in 2008 as far as I know, but we have Zero clients using that :)
据我所知，2008 年存在“更好”的解决方案，但我们有零客户端使用它:)
We created a table valued user defined function that takes a comma delimited string of IDs, and returns a table of IDs. The SQL then reads reasonably well, and none of it is dynamic, but there is still the annoying double overhead:
1. Client concatenates the IDs into the string
2. SQL Server parses the string to create a table of IDs
我们创建了一个表值用户定义函数，该函数采用逗号分隔的 ID 字符串，并返回一个 ID 表。然后 SQL 读取得相当好，而且都不是动态的，但仍然存在令人讨厌的双重开销：
1. 客户端将 ID 连接到字符串中
2. SQL Server 解析字符串以创建 ID 表
There are lots of ways of turning '1,2,3,4,5' into a table of IDs, but the Stored Procedure which uses the function ends up looking like...
有很多方法可以将 '1,2,3,4,5' 转换为 ID 表，但是使用该函数的存储过程最终看起来像......
CREATE PROCEDURE my_road_to_hell @IDs AS VARCHAR(8000)
AS
BEGIN
SELECT
*
FROM
myTable
INNER JOIN
dbo.fn_split_list(@IDs) AS [IDs]
ON [IDs].id = myTable.id
END
回答by idrosid
The fastest is to put the ids in another table and JOIN
最快的就是把id放到另一个表中然后JOIN
SELECT some_table.*
FROM some_table INNER JOIN some_other_table ON some_table.id = some_other_table.id
where some_other_table would have just one field (ids) and all values would be unique
其中 some_other_table 只有一个字段（id）并且所有值都是唯一的
回答by Tony Andrews
For a fixed set of IDs you can do:
对于一组固定的 ID，您可以执行以下操作：
SELECT * FROM some_table WHERE id IN (1001, 2002, 3003);
For a set that changes each time, you might want to create a table to hold them and then query:
对于每次更改的集合，您可能希望创建一个表来保存它们，然后查询：
SELECT * FROM some_table WHERE id IN
(SELECT id FROM selected_ids WHERE key=123);
Another approach is to use collections - the syntax for this will depend on your DBMS.
另一种方法是使用集合 - 其语法取决于您的 DBMS。
Finally, there is always this "kludgy" approach:
最后，总是有这种“笨拙”的方法：
SELECT * FROM some_table WHERE '|1001|2002|3003|' LIKE '%|' || id || '|%';
