查询篇-查询顾问产出(即正式学员交费金额)

2013年08月02日 10:11
点击率:7829

主查询:
Select
Student.ConsultantName as 顾问,
sum(Payment.PayMoney) as 金额,
Student.ConsultantID as ShowKey
from Payment,Student
where Payment.StudentID = Student.StudentID
and Payment.DateAndTime >= {@StartDate:开始日期}
and Payment.DateAndTime <= {@EndDate:结束日期}
and Payment.PaymentTypeID in (1,2)
group by Student.ConsultantID , Student.ConsultantName

子查询:
Select
Bill.StudentName as 所属学员姓名,
Bill.DateAndTime as 消费日期,
Bill.BillTypeID as 类型,
Bill.Pay as 应收金额,
Bill.Favourable as 优惠金额,
Bill.Payed as 实收金额,
BillItem.ProductName as 收费项名称
from Bill,billitem,Student
Where bill.billid=billitem.billid
and Bill.StudentID = Student.StudentID
and Bill.DateAndTime >= {@StartDate}
and Bill.DateAndTime <= {@EndDate}
and Student.ConsultantID = {@ShowKey}

这个查询在主查询设计上,参考Payment充值表流水,达到精准目的。 但在明细上调用的是消费单信息,实现交费的用途查询。如果学员存在预充值(充值尚未进行消费),或者退学等,明细里不作反映。



(把以上代码粘贴到《麦田培训学校管理软件》查询管理里,可直接使用)