sql in查询元素超过1000条的解决方案

目录
  • 一、场景描述
  • 二、解决方案
    • 1、方案一：核心思路是，将集合拆分，使用or 连接。
    • 2、方案二：用子查询（临时表）+关联查询
  • 总结

一、场景描述

查询机构下的人员，要同时查询出该机构及其所有下级机构的人员；比如查询北京市，不仅要查询出归属于北京市的人员，还要查询归属于出朝阳区、西城区、海淀区等等的人员。

对于这个需求，有以下几种解决思路：

1、之前的系统，他们的机构编码本身有关联，比如北京编码是001的话，昌平区的编码就是0012；这样的话，他们查询可以用like。这种方法的问题是like查询太慢，尤其在数据量大的时候，即使对编码加唯一索引，也只有查最根部机构时可以走索引（因为只有此时才符合最左前缀）。

2、我们的系统没有机构编码，唯一标识是雪花算法生成的19位id，没有关联；机构关系是通过一张关联表组合的起来的（适用于多业务系统，多机构树的情况）。这种情况，可以通过条件从关系表中查出所有下级机构id，然后查询时用in查询；为了方便查询，我们还建了一张机构关系冗余表。
但是这种方法也有一个问题，就是Oracle数据库in查询中元素，必须在1000以内。

二、解决方案

1、方案一：核心思路是，将集合拆分，使用or 连接。

select * from A where id in (1, 2, …, 1000) or id in (1001, …, 1999)

用mybatis的话就是这样

select * from test_1
<where>
 <if test="list != null and list.size > 0">
    (id IN
    <!-- 处理in的集合超过1000条时Oracle不支持的情况 -->
    <trim suffixOverrides=" OR id IN()">
        <foreach collection="list " item="Id" index="index" open="(" close=")">
            <if test="index != 0">
                <choose>
                    <when test="index % 1000 == 999">) OR id IN (</when>
                    <otherwise>,</otherwise>
                </choose>
            </if>
            #{Id}
        </foreach>
    </trim>
    )
 </if>

但是这种方法不好用，实测中3万条左右的机构，用这种查询查了好久都没出来，感觉数据库都要奔溃了。

2、方案二：用子查询（临时表）+关联查询

一般来说，超过1000多条的数据，肯定不是用户填写的，而是从其他地方查询出来的；我们可以将这些数据放到一个临时表中（用子查询实现），然后用内连接关联查询。

该查询中，机构关系冗余表SEP_SYSTEM_ORG_RELATION中有20多万条数据，但子查询走了我们创建的组合索引；整个查询只用了0.367秒

总结

到此这篇关于sql in查询元素超过1000条的解决方案的文章就介绍到这了,更多相关sql in查询元素超过1000条内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • mySQL中in查询与exists查询的区别小结

    一.关于exists查询 explain select * from vendor where EXISTS(select * from area where area_code = vendor_prov_code ) limit 10 以上是一个典型的exists查询的sql语句. 它的作用方式是这样的:每次从vendor表中查询出一条数据,然后将这条数据中的vendor_prov_code值传递到exists查询中进行执行,也就是进行子查询的执行. 如果子查询查到的数据就返回布尔值true

  • 在SQL查询中使用LIKE来代替IN查询的方法

    如下: 复制代码 代码如下: SELECT * FROM Orders WHERE OrderGUID IN('BC71D821-9E25-47DA-BF5E-009822A3FC1D','F2212304-51D4-42C9-AD35-5586A822258E') 可以看出直接在IN后面跟ID的集合需要将每一个ID都用单引号引起来.在实际应用中会遇到这么一种情况,在界面中收集的是一串GUID的拼接字符串,中间以逗号隔开,如果作为参数传到一个存储过程中执行,最终生成的语句会是下面这样: 复制代码

  • sql in查询元素超过1000条的解决方案

    目录 一.场景描述 二.解决方案 1.方案一:核心思路是,将集合拆分,使用or 连接. 2.方案二:用子查询(临时表)+关联查询 总结 一.场景描述 查询机构下的人员,要同时查询出该机构及其所有下级机构的人员:比如查询北京市,不仅要查询出归属于北京市的人员,还要查询归属于出朝阳区.西城区.海淀区等等的人员. 对于这个需求,有以下几种解决思路: 1.之前的系统,他们的机构编码本身有关联,比如北京编码是001的话,昌平区的编码就是0012:这样的话,他们查询可以用like.这种方法的问题是like查

  • mysqli多查询特性 实现多条sql语句查询

    mysqli相对于mysql有很多优势,建议大家使用,如果没有了解,可以查看mysql的基础教程: mysqli连接数据库 和 mysqli预处理prepare使用 .不仅如此,mysqli更是支持多查询特性,看下面这段php代码: 复制代码 代码如下: <?php $mysqli = new mysqli("localhost","root","","new"); $mysqli->query("set

  • SQL Server查询前N条记录的常用方法小结

    本文实例讲述了SQL Server查询前N条记录的常用方法.分享给大家供大家参考.具体如下: SQL Server查询前N条记录是我们经常要用到的操作,下面对SQL Server查询前N条记录的方法作了详细的介绍,如果您感兴趣的话,不妨一看. SQL Server查询前N条记录: 因为id可能不是连续的,所以不能用取得10<id<20的记录的方法. 有三种方法可以实现: 一.搜索前20条记录,指定不包括前10条 语句: 复制代码 代码如下: select top 20 * from tbl w

  • 一条SQL语句查询多个数据库

    需求:一条SQL语句从多个数据库查询出相关联的结果,输出到客户端 调查:spring框架可以配置多数据源:sql也可以将多个数据库附加到一个主数据库下 解决办法:用ATTACH DATABASE语句将多个库添加到主库下,实现功能 语法: ATTACH DATABASE 'DatabaseName' As 'Alias-Name'; 例子: ATTACH DATABASE 'C:\Users\Administrator\Desktop\sqlite_data.sq3' As 'sd'; 总结 以上

  • Oracle之SQL语句性能优化（34条优化方法）

    好多同学对sql的优化好像是知道的甚少,最近总结了以下34条仅供参考. (1)选择最有效率的表名顺序(只在基于规则的优化器中有效): ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. (2) WH

  • SQL参数化查询的另一个理由 命中执行计划

    SQL参数化查询的另一个理由 命中执行计划

    1概述 SQL语言的本质就是一串伪代码,表达的是做什么,而不是怎么做的意思.如其它语言一样,SQL语句需要编译之后才能运行,所以每一条SQL是需要通过编译器解释才能运行的(在这之间还要做SQL的优化).而这些步骤都是需要运行成本,所以在数据库中有一个叫做执行计划的东西,编译器会将编译过后的SQL存入执行计划当中,当遇到同样的SQL时,就直接调用执行计划来执行,而不需要再次编译. 通过对上面执行计划的认识,为了提高数据库运行的效率,我们需要尽可能的命中执行计划,这样就可以节省运行时间. 2相关SQ

  • sql分页查询几种写法

    sql分页查询几种写法

    关于SQL语句分页,网上也有很多,我贴一部分过来,并且总结自己已知的分页到下面,方便日后查阅 1.创建测试环境,(插入100万条数据大概耗时5分钟). create database DBTest use DBTest --创建测试表 create table pagetest ( id int identity(1,1) not null, col01 int null, col02 nvarchar(50) null, col03 datetime null ) --1万记录集 declar

  • Sql Server查询性能优化之不可小觑的书签查找介绍

    Sql Server查询性能优化之不可小觑的书签查找介绍

    小小程序猿SQL Server认知的成长 1.没毕业或工作没多久,只知道有数据库.SQL这么个东东,浑然分不清SQL和Sql Server Oracle.MySql的关系,通常认为SQL就是SQL Server 2.工作好几年了,也写过不少SQL,却浑然不知道索引为何物,只知道数据库有索引这么个东西,分不清聚集索引和非聚集索引,只知道查询慢了建个索引查询就快了,到头来索引也建了不少,查询也确实快了,偶然问之:汝建之索引为何类型?答曰:... 3.终于受到刺激开始奋发图强,买书,gg查资料终于知道

  • 数据库SQL SELECT查询的工作原理

    数据库SQL SELECT查询的工作原理

    作为Web开发人员,虽并非专业的DBA,但我们总是离不开数据库.一般开发员只会应用SQL的四条经典语句:select,insert,delete,update.以至于从来没有研究过它们的工作原理,在这里我们说一说select在数据库中的工作原理. B/S架构中最经典的话题无非于三层架构,可以大概分为数据层,业务逻辑层和表示层,而数据层的作用一般都是和数据库交互,例如查询记录.我们经常是写好查询SQL,然后调用程序执行SQL.但是它内部的工作流程是怎样的呢?先做哪一步,然后做哪一步等,我想还有大部

  • SQL基础查询和LINQ集成化查询

    SELECT SELECT 语句用于从表中选取数据,是 SQL 最基本的操作之一. 通过 SELECT 查询的结果被存储在一个结果表中(称为结果集). SQL SELECT 语法 只查询某个列的数据: SELECT [列名称] FROM [表名称] 查询所有列的数据 SELECT * FROM [表名称] SQL 查询所有数据 SELECT * FROM categories 查询一列或多列 SELECT category_id,category_name FROM categories 查询表

随机推荐