iterating through "IN" clause in SQL without a loop
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.
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 日
来自类别的问题 :
- sql 检查SQL Server表的更改?
- sql 数据库索引如何工作?
- sql 如何索引数据库列
- sql 删除名称以某个字符串开头的所有表
- sql 应该有人决定切换数据库系统
- sql 如何在SQL数据库表中选择第n行?
- sql 如何在SQL中请求随机行?
- sql 查找第二大值的最简单的SQL查询是什么?
- sql 在Oracle中进行多行插入的最佳方法是什么?
- sql 获取插入行的标识的最佳方法是什么?
- oracle 您要查询哪些表/视图以选择Oracle中架构中的所有表名?
- oracle 如何解决ORA-011033:正在进行ORACLE初始化或关闭
- oracle 如何在不同的表空间中导入oracle转储
- oracle Oracle中的双表是什么?
- oracle 您可以在Oracle中使用Microsoft Entity Framework吗?
- oracle 如何在Oracle中一起替换多个字符串
- oracle 查询表的外键关系
- oracle 如何确认数据库是Oracle以及使用SQL的版本?