主题：查询表合并

 

Dim t As Table = e.Form.controls("Table1").Table
Dim ary() As String = Functions.Execute("Xueqi")
Dim d1 As Date = Date.today
Dim d2 As Date = new Date(Date.today.Year, Date.today.Month,1)

查询1
t.Fill("Select  a.学期,报名教师,Format(上课日期,""yyyy-MM"") As 月份,Count(学生编号) As 学生数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) As 实收总数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) * 教师奖励百分率/100 As 奖励金额 Fr om {辅导班收费} a Inner JOIN {辅导教师工资} b ON a.学期 = b.学期  WHERE 上课日期 >= #" & d2.AddMonths(-1) & "# And 上课日期 <= #" & d2.AddDays(-1) & "#  And (结束日期 Is Null Or 结束日期 > 上课日期) GROUP BY a.学期,报名教师,Format(上课日期,""yyyy-MM""),教师奖励百分率","数据库",True)

查询表1
     t.Fill("Select  a.学期,分管领导,Format(上课日期,""yyyy-MM"") As 月份,Count(学生编号) As 学生数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) As 实收总数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) * 分管奖励百分率/100 As 奖励金额 Fr om {辅导班收费} a Inner JOIN {辅导教师工资} b ON a.学期 = b.学期  WHERE 上课日期 >= #" & d2.AddMonths(-1) & "# And 上课日期 <= #" & d2.AddDays(-1) & "#  And (结束日期 Is Null Or 结束日期 > 上课日期) GROUP BY a.学期,分管领导,Format(上课日期,""yyyy-MM""),分管奖励百分率","数据库",True)

查询合并

 

 

Dim t As Table = e.Form.controls("Table1").Table
Dim d1 As Date = Date.today
Dim d2 As Date = new Date(Date.today.Year, Date.today.Month,1)
Dim sql1 As String = "Select  a.学期,报名教师,Format(上课日期,""yyyy-MM"") As 月份,Count(学生编号) As 学生数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) As 实收总数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) * 教师奖励百分率/100 As 奖励金额 From {辅导班收费} a Inner JOIN {辅导教师工资} b ON a.学期 = b.学期  WHERE 上课日期 >= #" & d2.AddMonths(-1) & "# And 上课日期 <= #" & d2.AddDays(-1) & "#  And (结束日期 Is Null Or 结束日期 > 上课日期) GROUP BY a.学期,报名教师,Format(上课日期,""yyyy-MM""),教师奖励百分率"
Dim sql2 As String = "Select  a.学期,分管领导 As 报名教师,Format(上课日期,""yyyy-MM"") As 月份,Count(学生编号) As 学生数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) As 实收总数,Sum(IIf(退费金额 Is Null,缴费金额,缴费金额-退费金额)) * 分管奖励百分率/100 As 奖励金额 From {辅导班收费} a Inner JOIN {辅导教师工资} b ON a.学期 = b.学期  WHERE 上课日期 >= #" & d2.AddMonths(-1) & "# And 上课日期 <= #" & d2.AddDays(-1) & "#  And (结束日期 Is Null Or 结束日期 > 上课日期) GROUP BY a.学期,分管领导,Format(上课日期,""yyyy-MM""),分管奖励百分率"
t.Fill(sql1 & " union all " & sql2,"数据库",True)

谢谢老师，我把图片删了（有名字）。