查询篇-如何对收银员对账各收款方式进行汇总（SQL版）？

功能介绍

系统SQL版默认收银员对账功能有各种付款方式及金额，我们需要知道每个员工应交的总数和整个校区所有员工的应交总数这样的功能，就需要对原来的查询代码进行修改，用SQL查询 的with rollup 达成需要的目的。

如admin老师有刷卡，现金，中国银行三种收款方式，后一张图对这三个数据进行汇总，也就是admin一共收的费用是544639元，最后一个585349元是对全部的数据进行会汇总。

相关代码

主要是在group by 后面添加with rollup,此功能仅限于SQL数据库的用户，Access版不支持。

主查询

Select
Payment.UserID as 工号,
Payment.TrueName as 姓名,
Payment.PayMethod as 收费方式,
Sum(Payment.PayMoney) as 金额,
Payment.UserID as ShowKey ,
Payment.PayMethod as ShowKey1
from Payment
where
Payment.PaymentTypeID in (1,2)
and Payment.PostSchoolID in ( {@SchoolID:校区} )
and Payment.DateAndTime >= {@StartDate:开始日期}
and Payment.DateAndTime <= {@EndDate:结束日期}
Group by Payment.UserID,Payment.TrueName,Payment.PayMethod with rollup

子查询

Select
Payment.PaymentID as 充值号,
Payment.PaymentTypeName as 充值类型,
Payment.PayMethod as 收费方式,
Payment.DateAndTime as 交费日期,
Payment.StudentID as 学号,
Payment.StudentName as 学员,
Payment.PayMoney as 实收金额,
Payment.TrueName as 收款人
from Payment
where Payment.UserID = {@ShowKey}
and Payment.PayMethod= {@ShowKey1}
and Payment.PostSchoolID in ( {@SchoolID} )
and Payment.DateAndTime >= {@StartDate}
and Payment.DateAndTime <= {@EndDate}
and Payment.PaymentTypeID in (1,2)
order by Payment.DateAndTime desc