主题：统计与查询

 

 

老师，我看帮助里面，理解的是：

 

1、统计是都是对于已经存在的列进行统计是不？

2、比如我的人员信息表根本没有数量信息，我只能把男女人数，党员人数等先查询出来才进行下一步的统计查询？

统计是基于已经存在的数据进行统计。

 

 

Dim b As New SQLGroupTableBuilder("统计表1","person")
b.Groups.Adddef("organName")
b.Totals.Adddef("cardID",AggregateEnum.Count,"总人数")
b.Totals.Addexp("男","iif(sex = 男,1,0)")
b.Totals.Addexp("女","iif(sex=女,1,0)")
b.Build
MainTable = Tables("统计表1")

 

 

老师，这个为啥不行呀？

b.Totals.Addexp("男","iif(sex = '男',1,0)")
b.Totals.Addexp("女","iif(sex='女',1,0)")

老师，我是sqlserver数据库，我不太会case，when语句在这里如何替代iff

就是那两句代码该如何写？

参考：http://www.foxtable.com/webhelp/scr/2960.htm

Dim b As New SQLGroupTableBuilder("统计表1","person")
b.C
b.Groups.Adddef("organName")
b.Totals.Adddef("cardID",AggregateEnum.Count,"总人数")
b.Totals.Addexp("男","Case When sex = 男 Then 1 Else 0 End")
b.Totals.Addexp("女","Case When sex = 女 Then 1 Else 0 End")
b.Build
MainTable = Tables("统计表1")

 

说列名男女无效，这两个本来就是新命名的

b.Totals.Addexp("男","Case When sex = '男' Then 1 Else 0 End")
b.Totals.Addexp("女","Case When sex = '女' Then 1 Else 0 End")

Dim b As New SQLGroupTableBuilder("统计表1","person")
b.C
b.Groups.Adddef("organName","单位")
b.Totals.Adddef("cardID",AggregateEnum.Count,"人数")
Dim nms() As String = {"sex"}
For Each nm As String In nms  '利用表达式统计及格人数和优秀人数 
     b.Totals.Addexp("_男","Case When sex = '男' Then 1 Else 0 End")
     b.Totals.Addexp("_女","Case When sex = '女' Then 1 Else 0 End") 
Next
b.VerticalTotal = True
b.Build

For Each nm As String In nms  '对生成的统计表增加表达式列,计算及格率和优秀率
    DataTables("统计表1").DataCols.Add(nm &  "_男占比",Gettype(Double), nm &  "_男/人数" )
     Tables("统计表1").Cols(nm &  "_男占比").Move(Tables("统计表1").Cols(nm &  "_男").Index + 1)
    DataTables("统计表1").DataCols.Add(nm &  "_女占比",Gettype(Double), nm &  "_女/人数" )
     Tables("统计表1").Cols(nm &  "_女占比").Move(Tables("统计表1").Cols(nm &  "_女").Index + 1)
     DataTables("统计表1").DataCols(nm &  "_男占比").SetFormat("0.00%")
     DataTables("统计表1").DataCols(nm &  "_女占比").SetFormat("0.00%")
 Next
 MainTable = Tables("统计表1")

 

 

 

此主题相关图片如下：1.png

 

老师，

1、这个报错为啥呀

2、如何把数据库中的sex改为性别

改成

 

For Each nm As String In nms  '利用表达式统计及格人数和优秀人数 
     b.Totals.Addexp(nm & "_男","Case When sex = '男' Then 1 Else 0 End")
     b.Totals.Addexp(nm & "_女","Case When sex = '女' Then 1 Else 0 End") 
Next