[浏览完整版]
标题：显示【会员电话】【店名】【a有效积分】【a有效兑换】在临时表什么显示
1楼
qazlinle6 发表于：2023/11/28 19:43:00
2楼
有点蓝 发表于：2023/11/28 20:22:00Dim dt有效积分 As DataTable = cmd2.ExecuteReader
dt有效积分.DataCols.Add("a有效积分", GetType(Double))
For Each dr As DataRow In dt有效积分.Select("", "会员电话")
Dim pr1 As DataRow = DataTables("积分比例数据表").SQLFind("开始日期 <= '" & dr("日期") & "'And 结束日期 >= '" & dr("日期") & "' And ','+[店名]+',' like '%," & dr("店名") & ",%' And 商品素金非素='" & dr("商品素金非素") & "'")
' msgbox(dr("店名") & dr("日期") & pr1("积分比例"))
if pr1 isnot nothing then
dr("a有效积分") = dr("实收金额") / pr1("积分比例")
end if
Next
3楼
qazlinle6 发表于：2023/11/28 20:58:00 Dim bd1 As New SQLGroupTableBuilder("积分汇总", "销售合计") '这里使用后台统计,如果数据已经全部加载可以直接用GroupTableBuilder
bd1.C '外部数据表的话要指定数据源名称
bd1.Groups.AddDef("会员电话", "会员电话")
bd1.Groups.AddDef("店名", "店名")
bd1.Groups.AddDef("商品素金非素", "商品素金非素")
bd1.Totals.AddDef("实收金额", "实收金额")
bd1.Totals.AddDef("积分比例", "积分比例")
Dim pr1 As DataRow = DataTables("积分比例数据表").SQL1Find("开始日期 <= '" & dr("日期") & "'And 结束日期 >= '" & dr("日期") & "' And ','+[店名]+',' like '%," & dr("店名") & ",%' And 商品素金非素='" & dr("商品素金非素") & "'")
pr1("积分比例")
bd1.Build()
我想要这样
4楼
有点蓝 发表于：2023/11/28 21:02:00这个意思？
…… bd1.Totals.AddDef("实收金额", "实收金额")
bd1.Totals.AddDef("积分比例", "积分比例")
bd1.Build()
For Each dr As DataRow In DataTables("积分汇总").datarows
Dim pr1 As DataRow = DataTables("积分比例数据表").SQL1Find("开始日期 <= '" & dr("日期") & "'And 结束日期 >= '" & dr("日期") & "' And ','+[店名]+',' like '%," & dr("店名") & ",%' And 商品素金非素='" & dr("商品素金非素") & "'")
dr("积分比例") = pr1("积分比例")
next
5楼
qazlinle6 发表于：2023/11/28 21:12:00 Dim cmd2 As New SQLCommand
cmd2.C
Dim dt2 As DataTable
Dim sql有效积分 As String = " sel1ect 会员电话,店名,日期,商品素金非素,Sum([实收金额]) As 实收金额 From 销售合计 where [店名] ='周六福(1)' And [会员电话] Is Not Null And Year(日期) = " & Format(Date.Today, "yyyy") & " group by 会员电话,店名,日期,商品素金非素 "'
cmd2.CommandText = sql有效积分
Dim dt有效积分 As DataTable = cmd2.ExecuteReader
Dim b As New GroupTableBuilder("统计表1", dt有效积分)
b.Groups.AddDef("会员电话") '根据产品分组
b.Groups.AddDef("店名") '根据产品分组
b.Totals.AddDef("a有效积分") '对数量进行统计
b.Build()
Dim pr1 As DataRow = DataTables("积分比例数据表").SQLFind("开始日期 <= '" & dr("日期") & "'And 结束日期 >= '" & dr("日期") & "' And ','+[店名]+',' like '%," & dr("店名") & ",%' And 商品素金非素='" & dr("商品素金非素") & "'")
跟上面条件得到对应的 pr1("积分比例") 然后 dr("a有效积分") = dr("实收金额") / pr1("积分比例")
6楼
有点蓝 发表于：2023/11/28 21:22:00没看懂
7楼
qazlinle6 发表于：2023/11/28 21:31:00 Dim cmd2 As New SQLCommand cmd2.C Dim dt2 As DataTable Dim sql有效积分 As String = " sel1ect 会员电话,店名,日期,商品素金非素,Sum([实收金额]) As 实收金额 From 销售合计 where [店名] ='周六福(1)' And [会员电话] Is Not Null And Year(日期) = " & Format(Date.Today, "yyyy") & " group by 会员电话,店名,日期,商品素金非素 "' cmd2.CommandText = sql有效积分 Dim dt有效积分 As DataTable = cmd2.ExecuteReader Dim b As New GroupTableBuilder("统计表1", dt有效积分) b.Groups.AddDef("会员电话") '根据产品分组 b.Groups.AddDef("店名") '根据产品分组 b.Totals.AddDef("a有效积分") ' 是下面红色和上面粉色条件得到的表达式例a有效积分= dr("实收金额") / pr1("积分比例")然后显示在统计表1表里 b.Build() Dim pr1 As DataRow = DataTables("积分比例数据表").SQLFind("开始日期 <= '" & dr("日期") & "'And 结束日期 >= '" & dr("日期") & "' And ','+[店名]+',' like '%," & dr("店名") & ",%' And 商品素金非素='" & dr("商品素金非素") & "'") |
8楼
有点蓝 发表于：2023/11/28 21:51:00试试
Dim sql有效积分 As String = " select a.*,a.实收金额 / b.积分比例 as a有效积分 from (select 会员电话,店名,日期,商品素金非素,Sum([实收金额]) As 实收金额 From 销售合计 where [店名] ='周六福(1)' And [会员电话] Is Not Null And Year(日期) = " & Format(Date.Today, "yyyy") & " group by 会员电话,店名,日期,商品素金非素 ) as a inner join 积分比例数据表 as b on a.商品素金非素=b.商品素金非素 and b.开始日期 <=a.日期 and b.结束日期 >= a.日期 and ','+b.[店名]+',' like '%,' + a.店名 ',%'"
Dim sql有效积分 As String = " select a.*,a.实收金额 / b.积分比例 as a有效积分 from (select 会员电话,店名,日期,商品素金非素,Sum([实收金额]) As 实收金额 From 销售合计 where [店名] ='周六福(1)' And [会员电话] Is Not Null And Year(日期) = " & Format(Date.Today, "yyyy") & " group by 会员电话,店名,日期,商品素金非素 ) as a inner join 积分比例数据表 as b on a.商品素金非素=b.商品素金非素 and b.开始日期 <=a.日期 and b.结束日期 >= a.日期 and ','+b.[店名]+',' like '%,' + a.店名 ',%'"
9楼
qazlinle6 发表于：2023/11/28 22:00:00不行，显示404
10楼
有点蓝 发表于：2023/11/28 22:05:00请上传实例测试