1 DataTable dt = new DataTable(); 2 //嵌套的三元运算 牛叉到五体投地 3 object obj = dt.Compute("iif(1000=5,1000,iif(100>100,4001,2000))", null); 4 Response.Write(obj); 5 6 7 System.Data.DataTable table = new DataTable(); 8 //计算常量，可以没有初始化列 9 object test = table.Compute("1+1", ""); 10 Console.WriteLine(test); 11 12 string a = "123"; 13 System.Double b = 123; 14 decimal c = 123m; 15 Console.WriteLine(Convert.ToDecimal(a)); 16 //test=2; 17 18 test = table.Compute("1+1", "false"); 19 Console.WriteLine(test); 20 //test=2;常数计算和filter无关 21 22 test = table.Compute("abs(1)", ""); 23 Console.WriteLine(test); 24 //test=null，不知道为这个什么没有报错，而且返回null,其他的数学函数都会抱错 25 26 test = table.Compute("2%2", ""); 27 Console.WriteLine(test); 28 //test=0; 29 //其他函数参考下面的计算列 30 31 32 33 //初始化datatale 34 table.Columns.Add("id", typeof(string)); 35 table.Columns.Add("value", typeof(int)); 36 for (int i = 1; i <= 10; i++) 37 { 38 System.Data.DataRow dRow = table.NewRow(); 39 dRow["id"] = "id" + i.ToString(); 40 dRow["value"] = i; 41 table.Rows.Add(dRow); 42 } 43 44 45 46 //test = table.Compute("value+1", "true"); 47 /**/ 48 ////抛出异常，这里必须是聚合函数 49 50 51 52 //*************************************支持的聚合函数**********************// 53 54 //求数量 55 test = table.Compute("count(id)", "false"); 56 Console.WriteLine(test); 57 //test=0; 58 59 test = table.Compute("count(id)", "true"); 60 Console.WriteLine(test); 61 //test=10; 62 63 64 65 //求和 66 test = table.Compute("sum(value)", ""); 67 Console.WriteLine(test); 68 //test=55; 69 70 //test = table.Compute("sum(id)",""); 71 /**/ 72 ////抛出异常，这里不能是string 73 74 75 //平均 76 test = table.Compute("avg(value)", ""); 77 Console.WriteLine(test); 78 //test=5; 79 80 81 //最小 82 test = table.Compute("min(value)", ""); 83 Console.WriteLine(test); 84 //test=1; 85 86 //最大 87 test = table.Compute("max(value)", ""); 88 Console.WriteLine(test); 89 //test=10; 90 91 //统计标准偏差 92 test = table.Compute("StDev(value)", ""); 93 Console.WriteLine(test); 94 //test=3.02765035409749 95 96 //统计方差 97 test = table.Compute("Var(value)", ""); 98 Console.WriteLine(test); 99 //test=9.16666666666667 100 101 102 //复杂计算 103 test = table.Compute("max(value)/sum(value)", ""); 104 Console.WriteLine(test); 105 //test=0.181818181818182 106 107 /**/ 108 /*******************************************计算列*************************/ 109 110 System.Data.DataColumn column = new DataColumn("exp1", typeof(float)); 111 table.Columns.Add(column); 112 113 114 //简单计算 115 column.Expression = "value*2"; 116 test = table.Select("id='id1'")[0]["exp1"]; 117 Console.WriteLine(test); 118 //test=2; 119 120 //字符串函数 121 column.Expression = "len(id)"; 122 test = table.Select("id='id1'")[0]["exp1"]; 123 Console.WriteLine(test); 124 //test=3; 125 126 //字符串函数 127 column.Expression = "len(' '+id+' ')"; 128 test = table.Select("id='id1'")[0]["exp1"]; 129 Console.WriteLine(test); 130 //test=5; 131 132 //字符串函数 133 column.Expression = "len(trim(' '+id+' '))"; 134 test = table.Select("id='id1'")[0]["exp1"]; 135 Console.WriteLine(test); 136 //test=3; 137 138 //字符串函数 139 column.Expression = "substring(id,3,len(id)-2)"; 140 test = table.Select("id='id1'")[0]["exp1"]; 141 Console.WriteLine(test); 142 //test=1; //substring的起始字符位置为1不是0 143 144 //类型转换 145 column.Expression = "convert(substring(id,3,len(id)-2),'System.Int32')*1.6"; 146 test = table.Select("id='id1'")[0]["exp1"]; 147 Console.WriteLine(test); 148 //test=1.6; 149 150 //相当于sqlserver的isnull 151 column.Expression = "isnull(value,10)"; 152 test = table.Select("id='id1'")[0]["exp1"]; 153 Console.WriteLine(test); 154 //test=1; 155 156 //三元运算符,相当于sqlserver的case when 157 column.Expression = "iif(value>5,1000,2000)"; 158 test = table.Select("id='id1'")[0]["exp1"]; 159 Console.WriteLine(test); 160 //test=2000; 161 162 //like运算符 163 column.Expression = "iif(id like '%1',1000,2000)"; 164 test = table.Select("id='id1'")[0]["exp1"]; 165 Console.WriteLine(test); 166 //test=1000; 167 168 //in运算符 169 column.Expression = "iif(id not in('id1'),1000,2000)"; 170 test = table.Select("id='id1'")[0]["exp1"]; 171 Console.WriteLine(test); 172 //test=2000; 173 174 //嵌套的三元运算 175 column.Expression = "iif(value>5,1000,iif(id like '%1',4000,2000))"; 176 test = table.Select("id='id1'")[0]["exp1"]; 177 Console.WriteLine(test); 178 //test=4000; 179 180 181 //客户端计算所占总数的百分比 182 column.Expression = "value/sum(value)"; 183 test = table.Select("id='id1'")[0]["exp1"]; 184 Console.WriteLine(test); 185 //test=0.01818182 186 187 188 //客户端计算差值,比如nba常规赛的胜场差 189 column.Expression = "max(value)-value"; 190 test = table.Select("id='id1'")[0]["exp1"]; 191 Console.WriteLine(test); 192 //test=9 193 194 195 //***********************父子表计算*************************************/ 196 197 198 //初始化子表,父子表关系 199 DataTable tableChild = new DataTable(); 200 201 tableChild.Columns.Add("id", typeof(string)); 202 tableChild.Columns.Add("value", typeof(int)); 203 204 System.Data.DataSet ds = new DataSet(); 205 ds.Tables.Add(tableChild); 206 ds.Tables.Add(table); 207 DataRelation relation = new DataRelation("relation", table.Columns["id"], tableChild.Columns["id"]); 208 ds.Relations.Add(relation); 209 210 for (int i = 1; i <= 10; i++) 211 { 212 System.Data.DataRow dRow = tableChild.NewRow(); 213 dRow["id"] = "id1"; 214 dRow["value"] = i; 215 tableChild.Rows.Add(dRow); 216 } 217 218 219 //计算子表记录数 220 column.Expression = "count(child(relation).value)"; 221 test = table.Select("id='id1'")[0]["exp1"]; 222 Console.WriteLine(test); 223 //test=10; 224 225 226 227 //计算父子表的百分比 228 column.Expression = "value/sum(child(relation).value)"; 229 test = table.Select("id='id1'")[0]["exp1"]; 230 Console.WriteLine(test); 231 //test=0.01818182; 232 233 234 //计算父子表的差值,比如父表为库存数量，子表为订购数量，计算得出需要补充的数量 235 column.Expression = "iif(value-sum(child(relation).value)>0,0,value-sum(child(relation).value))"; 236 test = table.Select("id='id1'")[0]["exp1"]; 237 Console.WriteLine(test); 238 //test=-54; 239 240 //比较遗憾的是没有发现能够计算同比和环比的方法，而且计算列无法作为约束 241 //结束，DataTable可以让你尽量发挥聪明才智来减少繁杂的sql语句并且减轻服务器计算符合