查詢篇-如何對收銀員對賬各收款方式進行匯總（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