iterating through "IN" clause in SQL without a loop
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 日
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.
Use between instead of the in operator, e.g.
作者: Rohit Suthar 发布者: 27.12.2017 04:23
SELECT * FROM store_table WHERE store_a BETWEEN 1 AND 10