最近用Access数据库做一个微型应用,需要实现类似SQL Server里compute by语法的效果,网上google了一下,得知jet不支持,只好自己用.net模拟一个了,暂时只支持根据一个字段sum另一个字段,即sum(fieldA) compute by (filedB)。注意fieldA必须是decimal类型,如果是double类型恐怕需要修改源代码。
1
/**/
/// <summary>
2
/// 模拟SQL SERVER中的分类汇总ComputeBy语句
3
/// </summary>
4
/// <param name="sourceTable">源数据</param>
5
/// <param name="sumField">求和字段</param>
6
/// <param name="byField">分类字段</param>
7
/// <param name="needTotolSum">是否需要汇总合计</param>
8
/// <returns>目标数据源,重新绑定控件的DataSource属性后生效</returns>
9
public
static
DataTable ComputeBy(DataTable sourceTable,
string
sumField,
string
byField,
bool
needTotolSum)
10![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
11
int rowCount = sourceTable.Rows.Count;
12
if (rowCount == 0) return sourceTable;//数据量为0,不执行计算分类汇总
13
DataTable destTable = sourceTable.Copy();//深拷贝数据源
14
int insertTimes = 0;//插入次数,用于记录数据源与其拷贝之间的相对偏移量
15
int i = 0;
16
int j = 0;
17
decimal sum = 0;//分类汇总
18
decimal totolSum = 0;//合计
19
DataRow dr;
20
while (i < rowCount)
21![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
22
sum += (decimal)sourceTable.Rows[i][sumField];
23
j = i + 1;
24
if (j < rowCount && sourceTable.Rows[i][byField].ToString() != sourceTable.Rows[j][byField].ToString())
25![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
26
dr = destTable.NewRow();
27
dr[sumField] = sum;
28
totolSum += sum;
29
sum = 0;
30
destTable.Rows.InsertAt(dr, j + insertTimes);//插入分类汇总
31
insertTimes++;
32
}
33
i++;
34
}
35
dr = destTable.NewRow();
36
dr[sumField] = sum;
37
destTable.Rows.InsertAt(dr, j + insertTimes);
38
//分类汇总插入完毕,插入合计
39
if (needTotolSum)
40![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
41
totolSum += sum;
42
dr = destTable.NewRow();
43
dr[sumField] = totolSum;
44
destTable.Rows.Add(dr);
45
}
46
return destTable;
47
}
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![ExpandedBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![ContractedBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![ExpandedSubBlockStart.gif](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![ContractedSubBlock.gif](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
![](https://www.cnblogs.com/Images/dot.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)