iterating through "IN" clause in SQL without a loop

sql oracle

44 观看

2回复

37 作者的声誉

Is there a way to iterate through the "IN" clause in a SQL statement without having to implement some sort of loop?

My example is: 

SELECT *
FROM store_table
WHERE store_a IN (1,2,3,4,5,6,7,8,9,10);

Instead of having to list out all of the stores, is there a way to list write some sort of code that it will look like this: 

SELECT * 
FROM store_table
WHERE store_a IN (1,2,...10);

I understand that the above is not real SQL syntax but for examples sake I wanted to use it in that manner. I know that if you wanted to view all stores there are many different ways, syntactically to do so but I am just worried for the first 100 or the last 20 or etc....

The reason I am asking this is because if you had 1000 stores and you wanted to see the first 500, you would have some pretty long and annoying code.

Any help is appreciated!

作者: Alex Fields 的来源 发布者： 2017 年 12 月 27 日

回应 2

0像

865034 作者的声誉

If you want to see the first 500 stores, then use:

select st.*
from (select st.*
      from store_table st
      order by store_a
     ) st
where rownum <= 500;

This assumes one row per "store", which makes sense for a table with that name.

In Oracle 12C+, you can use fetch first 500 rows only and not have to use a subquery.

作者: Gordon Linoff 发布者: 2017 年 12 月 27 日

0像

294 作者的声誉

Use between instead of the in operator, e.g.

SELECT * 
FROM store_table
WHERE store_a BETWEEN 1 AND 10
作者: Rohit Suthar 发布者: 2017 年 12 月 27 日
32x32