表A(CustomerNumber,Total,BusinessDate,TransStatus)
表B(CustomerNumber,ID,Name,CompanyName)
要求是:
按companyname:公司名字 Total: 总数
CustomerNumber Name ID Total
个人编号 名字 身份证 总数"Select Sum(Total) as sTotal,A.CustomerNumber as sCus,B.CompanyName as sCom,A.Total as SAmt"
" From Trans A Join Customers B On A.CustomerNumber=B.CustomerNumber"
" Where A.BusinessDate between '" & TFrom & "' and '" & TTo & "'"
" And B.CompanyName<>'' And A.TransStatus=2 "
" Group by B.CompanyName"
分组该如何分组?
companyName:11 Total:500
CustomerNumber Name ID Total
1 1 1 200
2 2 2 200
3 3 3 100
表B(CustomerNumber,ID,Name,CompanyName)
要求是:
按companyname:公司名字 Total: 总数
CustomerNumber Name ID Total
个人编号 名字 身份证 总数"Select Sum(Total) as sTotal,A.CustomerNumber as sCus,B.CompanyName as sCom,A.Total as SAmt"
" From Trans A Join Customers B On A.CustomerNumber=B.CustomerNumber"
" Where A.BusinessDate between '" & TFrom & "' and '" & TTo & "'"
" And B.CompanyName<>'' And A.TransStatus=2 "
" Group by B.CompanyName"
分组该如何分组?
companyName:11 Total:500
CustomerNumber Name ID Total
1 1 1 200
2 2 2 200
3 3 3 100
下面是属于这个公司的个人的消费报告
CustomerNumber Name ID CompanyName Total
1 1 1 A 50
2 2 2 A 100
3 3 3 B 100
4 4 4 B 200
5 5 5 A 200
结果:
CompanyName:A Total:350
CustomerNumber Name ID Total
1 1 1 50
2 2 2 100
5 5 5 200CompanyName:B Total:300
CustomerNumber Name ID Total
3 3 3 100
4 4 4 200