处理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
}