C# DataTable中Compute方法用法集锦(数值/字符串/运算符/表等操作)
程序员文章站
2023-11-21 23:21:40
本文实例讲述了c# datatable中compute方法用法。分享给大家供大家参考,具体如下:
compute函数的参数就两个:expression,和filter。...
本文实例讲述了c# datatable中compute方法用法。分享给大家供大家参考,具体如下:
compute函数的参数就两个:expression,和filter。
expresstion是计算表达式,关于expression的详细内容请看这里:
http://msdn2.microsoft.com/zh-cn/library/system.data.datacolumn.expression(vs.80).aspx
而filter则是条件过滤器,类似sql的where条件。
datatable dt = new datatable(); //嵌套的三元运算 牛叉到五体投地 object obj = dt.compute("iif(1000=5,1000,iif(100>100,4001,2000))", null); response.write(obj); system.data.datatable table = new datatable(); //计算常量,可以没有初始化列 object test = table.compute("1+1", ""); console.writeline(test); string a = "123"; system.double b = 123; decimal c = 123m; console.writeline(convert.todecimal(a)); //test=2; test = table.compute("1+1", "false"); console.writeline(test); //test=2;常数计算和filter无关 test = table.compute("abs(1)", ""); console.writeline(test); //test=null,不知道为什么这个没有报错,而且返回null,其他的数学函数都会抱错 test = table.compute("2%2", ""); console.writeline(test); //test=0; //其他函数参考下面的计算列 //初始化datatale table.columns.add("id", typeof(string)); table.columns.add("value", typeof(int)); for (int i = 1; i <= 10; i++) { system.data.datarow drow = table.newrow(); drow["id"] = "id" + i.tostring(); drow["value"] = i; table.rows.add(drow); } //test = table.compute("value+1", "true"); /**/ ////抛出异常,这里必须是聚合函数 //*************************************支持的聚合函数**********************// //求数量 test = table.compute("count(id)", "false"); console.writeline(test); //test=0; test = table.compute("count(id)", "true"); console.writeline(test); //test=10; //求和 test = table.compute("sum(value)", ""); console.writeline(test); //test=55; //test = table.compute("sum(id)",""); /**/ ////抛出异常,这里不能是string //平均 test = table.compute("avg(value)", ""); console.writeline(test); //test=5; //最小 test = table.compute("min(value)", ""); console.writeline(test); //test=1; //最大 test = table.compute("max(value)", ""); console.writeline(test); //test=10; //统计标准偏差 test = table.compute("stdev(value)", ""); console.writeline(test); //test=3.02765035409749 //统计方差 test = table.compute("var(value)", ""); console.writeline(test); //test=9.16666666666667 //复杂计算 test = table.compute("max(value)/sum(value)", ""); console.writeline(test); //test=0.181818181818182 /**/ /*******************************************计算列*************************/ system.data.datacolumn column = new datacolumn("exp1", typeof(float)); table.columns.add(column); //简单计算 column.expression = "value*2"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=2; //字符串函数 column.expression = "len(id)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=3; //字符串函数 column.expression = "len(' '+id+' ')"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=5; //字符串函数 column.expression = "len(trim(' '+id+' '))"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=3; //字符串函数 column.expression = "substring(id,3,len(id)-2)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=1; //substring的起始字符位置为1不是0 //类型转换 column.expression = "convert(substring(id,3,len(id)-2),'system.int32')*1.6"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=1.6; //相当于sqlserver的isnull column.expression = "isnull(value,10)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=1; //三元运算符,相当于sqlserver的case when column.expression = "iif(value>5,1000,2000)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=2000; //like运算符 column.expression = "iif(id like '%1',1000,2000)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=1000; //in运算符 column.expression = "iif(id not in('id1'),1000,2000)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=2000; //嵌套的三元运算 column.expression = "iif(value>5,1000,iif(id like '%1',4000,2000))"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=4000; //客户端计算所占总数的百分比 column.expression = "value/sum(value)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=0.01818182 //客户端计算差值,比如nba常规赛的胜场差 column.expression = "max(value)-value"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=9 //***********************父子表计算*************************************/ //初始化子表,父子表关系 datatable tablechild = new datatable(); tablechild.columns.add("id", typeof(string)); tablechild.columns.add("value", typeof(int)); system.data.dataset ds = new dataset(); ds.tables.add(tablechild); ds.tables.add(table); datarelation relation = new datarelation("relation", table.columns["id"], tablechild.columns["id"]); ds.relations.add(relation); for (int i = 1; i <= 10; i++) { system.data.datarow drow = tablechild.newrow(); drow["id"] = "id1"; drow["value"] = i; tablechild.rows.add(drow); } //计算子表记录数 column.expression = "count(child(relation).value)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=10; //计算父子表的百分比 column.expression = "value/sum(child(relation).value)"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=0.01818182; //计算父子表的差值,比如父表为库存数量,子表为订购数量,计算得出需要补充的数量 column.expression = "iif(value-sum(child(relation).value)>0,0,value-sum(child(relation).value))"; test = table.select("id='id1'")[0]["exp1"]; console.writeline(test); //test=-54; //比较遗憾的是没有发现能够计算同比和环比的方法,而且计算列无法作为约束 //结束,datatable可以让你尽量发挥聪明才智来减少繁杂的sql语句并且减轻服务器计算符合
更多关于c#相关内容感兴趣的读者可查看本站专题:《c#常见控件用法教程》、《winform控件用法总结》、《c#数据结构与算法教程》、《c#面向对象程序设计入门教程》及《c#程序设计之线程使用技巧总结》
希望本文所述对大家c#程序设计有所帮助。
下一篇: android几种不同对话框的实现方式