Oracle SQL:如何在IN子句中使用1000多个项目 [英] Oracle SQL: How to use more than 1000 items inside an IN clause

问题描述

我有一条SQL语句，我想使用IN子句获取1200 ep_codes的数据.当我在IN子句中包含1000个以上的ep_codes时，Oracle表示不允许这样做.为了克服这个问题，我尝试如下更改SQL代码:

I have an SQL statement where I would like to get data of 1200 ep_codes by making use of IN clause. When I include more than 1000 ep_codes inside IN clause, Oracle says I'm not allowed to do that. To overcome this, I tried to change the SQL code as follows:

SELECT period, ...
FROM   my_view
WHERE  period = '200912'
       ...
       AND ep_codes IN (...1000 ep_codes...)
       OR  ep_codes IN (...200 ep_codes...)

该代码已成功执行，但结果很奇怪(在所有期间都获取了计算结果，而不仅仅是200912，这不是我想要的).是否应该在IN子句之间使用OR来执行此操作，还是应该执行两个单独的代码，一个使用1000，另一个使用200 ep_codes?

The code was executed succesfully but the results are strange (calculation results are fetched for all periods, not just for 200912, which is not what I want). Is it appropriate to do that using OR between IN clauses or should I execute two separate codes as one with 1000 and the other with 200 ep_codes?

帕斯卡·马丁(Pascal Martin)的解决方案运行完美.感谢所有提出宝贵建议的人.

Pascal Martin's solution worked perfectly. Thanks all who contributed with valuable suggestions.

推荐答案

不确定在IN()中使用这么多的值实际上是很好的-尤其是对于表演.

Not sure that using so many values in a IN() is that good, actually -- especially for performances.

当您说"结果很奇怪"时，可能是因为括号有问题吗?如果您尝试此操作，而不是您的建议，该怎么办?

When you say "the results are strange", maybe this is because a problem with parenthesis ? What if you try this, instead of what you proposed :

SELECT ...
FROM ...
WHERE ...
      AND (
          ep_codes IN (...1000 ep_codes...)
          OR  ep_codes IN (...200 ep_codes...)
      )

它会使结果减少奇怪吗?

这篇关于Oracle SQL:如何在IN子句中使用1000多个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！