选择出现在 2927 个 ID 列表中的所有行

当未达到 1000 的限制时语句会如何: SELECT * FROM表名其中tablename.id在(67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204，2211,2212,2332,2576)； 解决方案 这是另一种方法，通过使用公共表表达式 (CTE) 将 ID 转换为逻辑表，然后像往常一样加入.用这种方式思考可能更容易理解:-- 构建 ID 列表.数据(str)为(选择 '67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204,2211,2212,2332,2576'从双重),-- 使用逗号作为分隔符将列表转换为表格.想一想-- 就像内存中的临时表.id_list(id) 为 (选择 regexp_substr(str, '(.*?)(,|$)', 1, level, NULL, 1)从数据按级别连接 <= regexp_count(str, ',') + 1)-- 从主表中选择数据，加入到id_list "temp" 表中-- ID 匹配.选择表名.*从表名，id_list其中 tablename.id = id_list.id;I have a list of 2927 id's. I want to get all rows which id's are in that list. How can I achieve this? It is a comma seperated list of id's. An in statement won't work since there is a limit of 1000. I've tried solutions like this Loop through pre defined values but it doesn't do what I expect.I'm using toad and I wish to see the rows in the datagrid (multiple rows, multiple columns).Thanks in advance!How a list of id's may look like: 67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204,2211,2212,2332,2576,...How the statement would be when the limit of 1000 isn't reached:Select * from tablename where tablename.id in (67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204,2211,2212,2332,2576); 解决方案 Here's another way to approach it by turning the IDs into a logical table using a Common Table Expression (CTE) then joining like usual. Might be easier to get your head around it thinking of it this way:-- Build the list of IDs.with data(str) as ( select '67,122,173,256,284,285,288,289,291,294,296,298,301,320,346,359 ,366,425,428,454,528,573,576,584,593,654,654,694,722,838,1833,1976,1979,1979,2002 ,2004,2005,2045,2083,2109,2114,2126,2126,2157,2204,2204,2211,2212,2332,2576' from dual),-- Turn the list into a table using the comma as the delimiter. Think of it-- like a temp table in memory.id_list(id) as ( select regexp_substr(str, '(.*?)(,|$)', 1, level, NULL, 1) from data connect by level <= regexp_count(str, ',') + 1)-- Select data from the main table, joining to the id_list "temp" table where-- the ID matches.select tablename.*from tablename, id_listwhere tablename.id = id_list.id; 这篇关于选择出现在 2927 个 ID 列表中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..