DataRow[] drgreenStandard = dt_zhEvaStandard_xs.Select("节点编号='" + PartJdbh + "'and 等级='绿色等级'");
sqlstr = "select b.NodeID from 三维数值模拟结果表 a,三维数值模拟节点表 b where a.NodeID=b.NodeID and a.模型ID='HDCF_KSH'and b.模型ID='HDCF_KSH'" +
"and a.PEEQ>='" + Convert.ToDouble(drgreenStandard[0]["下限"]) + "'and a.PEEQ<'" + Convert.ToDouble(drgreenStandard[0]["上限"]) +
"'and b.Ox>'" + (Convert.ToDouble(dr[0]["点1x"])+
"'and b.Ox<'" + (Convert.ToDouble(dr[0]["点1x"]) + "'"; SqlDataAdapter da_dg_green = new SqlDataAdapter(sqlstr, conn);
DataTable dt_dg_green = new DataTable();
da_dg_green.Fill(dt_dg_green);
da_dg_green.Dispose();
List<string> listgreen = new List<string>();
for (int i = 0; i < dt_dg_green.Rows.Count; i++)
{
listgreen.Add(dt_dg_green.Rows[i]["NodeID"].ToString().Trim());
} string allbhgreen = "";
foreach (string str in listgreen)
{
allbhgreen += "'" + str.Replace("'", "''") + "' ,";
} if (allbhgreen.Length > 1)
{
allbhgreen = allbhgreen.Remove(allbhgreen.Length - 1); sqlstr = "select 单元体积 from 三维数值模拟单元表 where 模型ID='HDCF_KSH' and ( n1 in (" + allbhgreen + ")" + " and n2 in (" + allbhgreen + ")" + " and n3 in (" + allbhgreen + ")" +
" and n4 in (" + allbhgreen + ")" + " and n5 =''" + ") or ( n1 in (" + allbhgreen + ")" + " and n2 in (" + allbhgreen + ")" + " and n3 in (" + allbhgreen + ")" +
"and n4 in (" + allbhgreen + ")" + " and n5 in (" + allbhgreen + ")" + "and n6 in (" + allbhgreen + ")" + " and n7 =''" + ") or ( n1 in (" + allbhgreen + ")" +
"and n2 in (" + allbhgreen + ")" + "and n3 in (" + allbhgreen + ")" + "and n4 in (" + allbhgreen + ")" + "and n5 in (" + allbhgreen + ")" +
"and n6 in (" + allbhgreen + ")" + "and n7 in (" + allbhgreen + ")" + "and n8 in (" + allbhgreen + ")" + ")";
SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);
da.SelectCommand.CommandTimeout = 180;
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
foreach (DataRow drall in dt.Rows)
{
if (!Convert.IsDBNull(drall["单元体积"]))
{
output[0] += Convert.ToDouble(drall["单元体积"]);
}
} }
三维数值模拟结果表 ,三维数值模拟节点, 三维数值模拟单元表 分别有30万条和 30万 和80万。我现在是先从结果表和节点表找出满足条件的节点,然后到单元表找到这些结点对应的单元体积,可是这一步在搜索分成了三种情况:第一种:n1 n2 n3 n4 在满足条件的节点里 n5为空 。第二种:n1 n2 n3 n4 n5 n6 全在搜索的节点里n7空;第三种 n1到n8全在搜索出的节点里;在这一步我的搜索速度很慢,当allbhgreen 有两千多个数时,需要大概30多秒,请问这个查询语句怎么写能把速度提高的快一些?????
sqlstr = "select b.NodeID from 三维数值模拟结果表 a,三维数值模拟节点表 b where a.NodeID=b.NodeID and a.模型ID='HDCF_KSH'and b.模型ID='HDCF_KSH'" +
"and a.PEEQ>='" + Convert.ToDouble(drgreenStandard[0]["下限"]) + "'and a.PEEQ<'" + Convert.ToDouble(drgreenStandard[0]["上限"]) +
"'and b.Ox>'" + (Convert.ToDouble(dr[0]["点1x"])+
"'and b.Ox<'" + (Convert.ToDouble(dr[0]["点1x"]) + "'"; SqlDataAdapter da_dg_green = new SqlDataAdapter(sqlstr, conn);
DataTable dt_dg_green = new DataTable();
da_dg_green.Fill(dt_dg_green);
da_dg_green.Dispose();
List<string> listgreen = new List<string>();
for (int i = 0; i < dt_dg_green.Rows.Count; i++)
{
listgreen.Add(dt_dg_green.Rows[i]["NodeID"].ToString().Trim());
} string allbhgreen = "";
foreach (string str in listgreen)
{
allbhgreen += "'" + str.Replace("'", "''") + "' ,";
} if (allbhgreen.Length > 1)
{
allbhgreen = allbhgreen.Remove(allbhgreen.Length - 1); sqlstr = "select 单元体积 from 三维数值模拟单元表 where 模型ID='HDCF_KSH' and ( n1 in (" + allbhgreen + ")" + " and n2 in (" + allbhgreen + ")" + " and n3 in (" + allbhgreen + ")" +
" and n4 in (" + allbhgreen + ")" + " and n5 =''" + ") or ( n1 in (" + allbhgreen + ")" + " and n2 in (" + allbhgreen + ")" + " and n3 in (" + allbhgreen + ")" +
"and n4 in (" + allbhgreen + ")" + " and n5 in (" + allbhgreen + ")" + "and n6 in (" + allbhgreen + ")" + " and n7 =''" + ") or ( n1 in (" + allbhgreen + ")" +
"and n2 in (" + allbhgreen + ")" + "and n3 in (" + allbhgreen + ")" + "and n4 in (" + allbhgreen + ")" + "and n5 in (" + allbhgreen + ")" +
"and n6 in (" + allbhgreen + ")" + "and n7 in (" + allbhgreen + ")" + "and n8 in (" + allbhgreen + ")" + ")";
SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);
da.SelectCommand.CommandTimeout = 180;
DataTable dt = new DataTable();
da.Fill(dt);
da.Dispose();
foreach (DataRow drall in dt.Rows)
{
if (!Convert.IsDBNull(drall["单元体积"]))
{
output[0] += Convert.ToDouble(drall["单元体积"]);
}
} }
三维数值模拟结果表 ,三维数值模拟节点, 三维数值模拟单元表 分别有30万条和 30万 和80万。我现在是先从结果表和节点表找出满足条件的节点,然后到单元表找到这些结点对应的单元体积,可是这一步在搜索分成了三种情况:第一种:n1 n2 n3 n4 在满足条件的节点里 n5为空 。第二种:n1 n2 n3 n4 n5 n6 全在搜索的节点里n7空;第三种 n1到n8全在搜索出的节点里;在这一步我的搜索速度很慢,当allbhgreen 有两千多个数时,需要大概30多秒,请问这个查询语句怎么写能把速度提高的快一些?????
解决方案 »
- mysql中数据是中文的没错,但在myEclipse里面出来的就是乱码了,怎么解决
- 请大家帮忙修改一下触发器,谢谢
- powerdesigner 生成测试数据,Test Data Profile 使用ODBC时,字符被截,???
- 怎么生成交叉统计表??新手问题,100分
- 求一sql语句....统计每个种类的最后一条记录...
- [求助] 如何按指定的多个销售额范围进行分组显示 ?
- 请教一个SQL,怎样把一个表里的一部分内容插入到一个新表里?在线......
- 这种数据怎样表示??
- VFP 6.0 编译成EXE文件后执行后一闪即消失如何解决。急
- {急}[SQLServer]UPDATE 失败,因为下列 SET 选项的设置不正确: 'ARITHABORT'。{急}
- SQL时间比较问题
- 求SQL语句
就是在给出这种语句:
select b.NodeID
from 三维数值模拟结果表 a,
三维数值模拟节点表 b
where a.NodeID=b.NodeID and a.模型ID='HDCF_KSH'
and b.模型ID='HDCF_KSH'and a.PEEQ>=Convert.ToDouble(drgreenStandard[0][...而不是上面的if-else,for循环等代码哈。你可以用sql profiler来捕获到这样的语句的哈
对了 里面的allbhgreen 这个是in 括号里面的把,里面是什么样的呢