Oracle # 大数据处理之in(Oracle SQL in 超过1000 的解决方案)

淩亂°似流年 2022-02-26 06:12 1510阅读 0赞

处理oracle sql 语句in子句中（where id in (1, 2, ..., 1000, 1001)），如果子句中超过1000项就会报错。  
这主要是oracle考虑性能问题做的限制。如果要解决次问题，可以用 where id (1, 2, ..., 1000) or id (1001, ...)

解决方案1：

public string GetSqlIn( string sqlParam, string columnName )
    
            {
    
                int width = sqlParam.IndexOf( "'", 1 ) - 1;
    
                string temp = string.Empty;
    
                    
    
                for( int i = 0; i < sqlParam.Length; i += 1000 * ( width + 3 ) )
    
                {
    
                    if( i + 1000 * ( width + 3 ) - 1 < sqlParam.Length )
    
                    {
    
                        temp = temp + sqlParam.Substring( i, 1000 * ( width + 3 ) - 1 )
    
                            + ") OR " + columnName + " IN (";
    
                    }
    
                    else
    
                    {
    
                        temp = temp + sqlParam.Substring( i, sqlParam.Length - i );
    
                    }
    
                }
    
    
    
                return temp;
    
            }

使用这个方法的返回值，代码如下：

System.Text.StringBuilder sql = new System.Text.StringBuilder("");
    
    sql.Append ( " SELECT " );
    
    sql.Append ( " T.A" );
    
    sql.Append ( " FROM TEST T" );   
    
    sql.Append ( " WHERE 1=1 " );
    
    
    
    if( Col.Length > 0 )
    
    {
    
         string sqlStr = GetSqlIn( Col, "ColName" );
    
         sql.Append ( " AND T.Col IN ( " + sqlStr + " )" );
    
    }
    
    sql.Append ( " ORDER BY T.A" );

运行后得到的SQL字符串格式为：

select t.* from TEST t where t.A in (59,60) or t.A in (61,62)

备注：来自于[hoojo][]

说明：保证每个括号内个数不超过一千，就可以。执行效率可能比较低下。

--------------------

解决方案2：

建立一个中间的temp表存在查询条件，在数据库内部进行直接查询

select *  from table_1 where column_1 in ( select column_2 from table_2_temp )

--------------------

解决方案3：

如果IN里面的数据是从别的表取的话，可以直接这样编写sql语句

select *  from table_1 where column_1 in ( select column_2 from table_2 ，....... )

备注：作者xiaomgee

--------------------

解决方案4：

**where not in**   
对于where column ont in (A,B,C,D……) 这样的语句，与其等价的是where column not in (A,B……) and column not in(C,D……)。

--------------------

## 最后分享一个类库: ##

public class StringHelper
        {
            #region List<string>转换为string
            /// <summary>
            /// List<string>转换为string
            /// </summary>
            /// <param name="list"></param>
            /// <returns></returns>
            public static string listToString(List<string> list)
            {
                if (list == null)
                {
                    return null;
                }
                string ss = "";
                foreach (string error in list)
                {
                    ss = error + "," + ss;
                }
                ss = ss.Substring(0, ss.Length - 1);
                return ss;
            }
            #endregion
    
            #region List<string>转换为string
            /// <summary>
            /// List<string>转换为string
            /// </summary>
            /// <param name="list"></param>
            /// <returns></returns>
            public string listToStringNew(List<string> list)
            {
                if (list == null)
                {
                    return null;
                }
                string ss = "";
                foreach (string error in list)
                {
                    ss = "'" + error + "'," + ss;
                }
                ss = ss.Substring(0, ss.Length - 1);
                return ss;
            }
            #endregion
    
            #region IN 查询时出现ORA-01795:列表中的最大表达式数为1000解决方法
            /// <summary>
            /// IN 查询时出现ORA-01795:列表中的最大表达式数为1000解决方法
            /// </summary>
            /// <param name="id"></param>
            /// <param name="list"></param>
            /// <returns></returns>
            public static String getString(String id, List<String> list)
            {
                StringBuilder sb = new StringBuilder();
                String returnString = "";
                if (list.Count() == 0 || null == list)
                {
                    returnString = sb.Append(id).Append("=''").ToString();
                }
                for (int i = 0; i < list.Count; i++)
                {
                    if (i == 0)
                    {
                        sb.Append(id);
                        sb.Append(" in (");
                    }
                    sb.Append("'");
                    sb.Append(list[i].ToString());
                    sb.Append("'");
                    if (i >= 900 && i < list.Count() - 1)
                    {
                        if (i % 900 == 0)
                        {
                            sb.Append(") or ");
                            sb.Append(id);
                            sb.Append(" in (");
                        }
                        else
                        {
                            sb.Append(",");
                        }
                    }
                    else
                    {
                        if (i < list.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    if (i == list.Count - 1)
                    {
                        sb.Append(")");
                    }
                }
                returnString = sb.ToString();
                return returnString;
            }
            #endregion
    
            #region IN 查询时出现ORA-01795:列表中的最大表达式数为1000解决方法
            /// <summary>
            /// IN 查询时出现ORA-01795:列表中的最大表达式数为1000解决方法
            /// </summary>
            /// <param name="id"></param>
            /// <param name="list"></param>
            /// <returns></returns>
            public String getStringNew(String id, List<String> list)
            {
                StringBuilder sb = new StringBuilder();
                String returnString = "";
                if (list.Count() == 0 || null == list)
                {
                    returnString = sb.Append(id).Append("=''").ToString();
                }
                for (int i = 0; i < list.Count; i++)
                {
                    if (i == 0)
                    {
                        sb.Append(id);
                        sb.Append(" in (");
                    }
                    sb.Append("'");
                    sb.Append(list[i].ToString());
                    sb.Append("'");
                    if (i >= 900 && i < list.Count() - 1)
                    {
                        if (i % 900 == 0)
                        {
                            sb.Append(") or ");
                            sb.Append(id);
                            sb.Append(" in (");
                        }
                        else
                        {
                            sb.Append(",");
                        }
                    }
                    else
                    {
                        if (i < list.Count - 1)
                        {
                            sb.Append(",");
                        }
                    }
                    if (i == list.Count - 1)
                    {
                        sb.Append(")");
                    }
                }
                returnString = sb.ToString();
                return returnString;
            }
            #endregion
    
        }

[hoojo]: https://www.cnblogs.com/hoojo/