Oracle # 大数据处理之in(Oracle SQL in 超过1000 的解决方案) 淩亂°似流年 2022-02-26 06:12 1456阅读 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/
还没有评论,来说两句吧...