主题：外部库由ACCESS转为SQL后代码出错

呵呵，不好意思，今天很忙，做个简单例子上来吧，将这个SQL表也传上来.

下面的代码测试通过：

Dim b As New SQLGroupTableBuilder("统计表1","种鸡生产记录")
b.Connection Name = "zjxt"
b.Filter="year([日期])=" & 2009 & " and month([日期])=" & 7
b.Groups.AddDef("日期", DateGroupEnum.Year, "年")
b.Groups.AddDef("日期", DateGroupEnum.month, "月")
b.Groups.AddDef("日期", DateGroupEnum.Day, "日")
b.Totals.AddDef("合格蛋数")
b.Totals.AddDef("次蛋数")
b.Totals.AddDef("破蛋数")
b.Totals.AddExp("总蛋数","ISNULL([合格蛋数],0) + ISNUll([次蛋数],0) + ISNULL([破蛋数],0)")
b.Totals.AddExp("产蛋率","(ISNULL([合格蛋数],0) + ISNUll([次蛋数],0) + ISNULL([破蛋数],0)) / [存栏母鸡] * 100","产蛋率(%)_存栏鸡",AggregateEnum.Average)
b.Totals.AddExp("入舍产蛋率","(ISNULL([合格蛋数],0) + ISNUll([次蛋数],0) + ISNULL([破蛋数],0)) / [入舍母鸡]*100","产蛋率(%)_入舍鸡",AggregateEnum.Average)
b.Totals.AddDef("死亡公鸡","死亡数_♂")
b.Totals.AddDef("死亡母鸡","死亡数_♀")
b.Totals.AddDef("淘汰公鸡","淘汰数_♂")
b.Totals.AddDef("淘汰母鸡","淘汰数_♀")
b.Totals.AddDef("转入公鸡","转入_♂")
b.Totals.AddDef("转入母鸡","转入_♀")
b.Totals.AddDef("转出公鸡","转出_♂")
b.Totals.AddDef("转出母鸡","转出_♀")
b.Totals.AddDef("存栏公鸡","存栏_♂")
b.Totals.AddDef("存栏母鸡","存栏_♀")
b.Totals.AddDef("销售淘汰公鸡","销售淘汰_♂")
b.Totals.AddDef("销售淘汰母鸡","销售淘汰_♀")
b.Totals.AddExp("公死淘率","([死亡公鸡]+[淘汰公鸡])/[存栏公鸡]*100","死淘率(%)_♂",AggregateEnum.Average)
b.Totals.AddExp("母死淘率","([死亡母鸡]+[淘汰母鸡])/[存栏母鸡]*100","死淘率(%)_♀",AggregateEnum.Average)
b.Totals.AddExp("公死率","[死亡公鸡]/[存栏公鸡]*100","死亡率(%)_♂",AggregateEnum.Average)
b.Totals.AddExp("母死率","[死亡母鸡]/[存栏母鸡]*100","死亡率(%)_♀",AggregateEnum.Average)
b.Totals.AddExp("公淘率","[淘汰公鸡]/[存栏公鸡]*100","淘汰率(%)_♂",AggregateEnum.Average)
b.Totals.AddExp("母淘率","[淘汰母鸡]/[存栏母鸡]*100","淘汰率(%)_♀",AggregateEnum.Average)
b.Totals.AddDef("公耗料","耗料量(kg)_♂")
b.Totals.AddDef("母耗料","耗料量(kg)_♀")
b.Totals.AddExp("公羽均耗料","[公耗料]/[存栏公鸡]*1000","羽均耗料(g)_♂",AggregateEnum.Average)
b.Totals.AddExp("母羽均耗料","[母耗料]/[存栏母鸡]*1000","羽均耗料(g)_♀",AggregateEnum.Average)
b.Totals.AddExp("合格率","[合格蛋数]/([合格蛋数]+[次蛋数]+[破蛋数])*100","种蛋_合格率(%)",AggregateEnum.Average)
b.Totals.AddExp("破损率","[破蛋数]/([合格蛋数]+[次蛋数]+[破蛋数])*100","种蛋_破损率(%)",AggregateEnum.Average)
b.Totals.AddExp("公母配比","[存栏母鸡]/[存栏公鸡]",AggregateEnum.Average)
b.Totals.AddExp("料蛋比","(ISNULL([公耗料],0) + ISNULL([母耗料],0)) / [合格蛋数] * 1000","料蛋比(克/枚)",AggregateEnum.Average)
b.Decimals=3
b.Build

查看生成的表结构，可以发现这些XX率的类型都是整数，原因知道， 解决办法也就出来了：

Dim b As New SQLGroupTableBuilder("统计表1","种鸡生产记录")
b.C
b.Filter="year([日期])=" & 2009 & " and month([日期])=" & 7
b.Groups.AddDef("日期", DateGroupEnum.Year, "年")
b.Groups.AddDef("日期", DateGroupEnum.month, "月")
b.Groups.AddDef("日期", DateGroupEnum.Day, "日")
b.Totals.AddDef("合格蛋数")
b.Totals.AddDef("次蛋数")
b.Totals.AddDef("破蛋数")
b.Totals.AddExp("总蛋数","ISNULL([合格蛋数],0) + ISNUll([次蛋数],0) + ISNULL([破蛋数],0)")
b.Totals.AddExp("产蛋率","(ISNULL([合格蛋数],0) + ISNUll([次蛋数],0) + ISNULL([破蛋数],0) + 0.1 - 0.1) / [存栏母鸡] * 100","产蛋率(%)_存栏鸡",AggregateEnum.Average)
b.Totals.AddExp("入舍产蛋率","(ISNULL([合格蛋数],0) + ISNUll([次蛋数],0) + ISNULL([破蛋数],0) + 0.1 - 0.1) / [入舍母鸡]*100","产蛋率(%)_入舍鸡",AggregateEnum.Average)
b.Build

例如加减乘除模拟mod函数

select 9 - 9 / 7 * 7

得到的结果就是2

例如取得订单明细表数量列与7相除后的余数：

select 数量 - Floor(数量/7) * 7  From {订单明细}

1、SQL没有iif语句，需要用case语句代替

2、我用Select开头是用来直接在sql执行窗口执行，以便验证是否可行的，作为统计表达式的时候，是不需要用Select的

例如：

Dim b As New SQLGroupTableBuilder("统计表5","种鸡生产记录")
b.C
b.Groups.AddDef("批次")
b.Groups.AddDef("品种")
b.Groups.AddDef("周龄")
b.Totals.AddDef("合格蛋数")
b.Totals.AddDef("次蛋数")
b.Totals.AddDef("破蛋数")
b.Totals.AddExp("存栏公鸡","Case [日龄] - [日龄] / 7 * 7 When 0 Then [存栏公鸡] Else 0 End","周末存栏_公鸡")
b.Decimals=3
b.Build