Dim d As Date = new Date(Date.Today.Year, Date.Today.Month, 1)
Dim jb As New SQLJoinTableBuilder("查询表1","消费信息")
jb.AddCols("订单编号","就餐时间")
jb.AddExp("数量","就餐人数")
jb.AddExp("分类","'就餐人数'")
jb.Filter = "就餐时间 >= #" & d & "# and 就餐时间 < #" & d.AddMonths(1) & "#"
Dim jb1 As New SQLJoinTableBuilder("查询表1","消费信息")
jb1.AddTable("消费信息","订单编号","消费明细","订单编号")
jb1.AddCols("{消费信息}.订单编号","就餐时间",True)
jb1.AddExp("数量","Sum(累计消费)")
jb1.AddExp("分类","'消费总额'")
jb1.Filter = "就餐时间 >= #" & d & "# and 就餐时间 < #" & d.AddMonths(1) & "#"
Dim jb2 As New SQLJoinTableBuilder("查询表1","消费信息")
jb2.AddTable("消费信息","订单编号","消费明细","订单编号")
jb2.AddCols("{消费信息}.订单编号","就餐时间",True)
jb2.AddExp("数量","Sum(累计消费/就餐人数)")
jb2.AddExp("分类","'人均消费'")
jb2.Filter = "就餐时间 >= #" & d & "# and 就餐时间 < #" & d.AddMonths(1) & "#"
jb.Union(jb1,True)
jb.Union(jb2,True)
Dim g As New CrossTableBuilder("统计表2",jb.BuildSql)
g.HGroups.AddDef("订单编号")
g.HGroups.AddDef("分类")
g.VGroups.AddDef("就餐时间", "{0}月")
g.VGroups.AddDef("就餐时间", DateGroupEnum.Day, "{0}日")
g.Totals.AddDef("数量", "数量")
g.Build()
Dim t As Table = Tables("统计表2")
Dim cwidth As String = "订单编号|98|分类|98"
For i As Integer = 1 To Date.DaysInMonth(d.Year, d.Month)
Dim flag As Boolean = True
For Each c As Col In t.Cols
If c.Caption = (d.Month & "月_" & i & "日") Then
flag = False
cwidth &= "|" & c.Name & "|98"
End If
Next
If flag Then
t.DataTable.DataCols.Add(d.Month & "月_" & i & "日", Gettype(Date))
cwidth &= "|" & d.Month & "月_" & i & "日" & "|98"
End If
Next
t.SetColVisibleWidth(cwidth)
MainTable = t
加好友 
发短信
Post By：2014/7/13 14:08:00 [只看该作者]
