查詢(xún)篇-如何對(duì)收銀員對(duì)賬各收款方式進(jìn)行匯總（SQL版）？

功能介紹

系統(tǒng)SQL版默認(rèn)收銀員對(duì)賬功能有各種付款方式及金額，我們需要知道每個(gè)員工應(yīng)交的總數(shù)和整個(gè)校區(qū)所有員工的應(yīng)交總數(shù)這樣的功能，就需要對(duì)原來(lái)的查詢(xún)代碼進(jìn)行修改，用SQL查詢(xún) 的with rollup 達(dá)成需要的目的。

如admin老師有刷卡，現(xiàn)金，中國(guó)銀行三種收款方式，后一張圖對(duì)這三個(gè)數(shù)據(jù)進(jìn)行匯總，也就是admin一共收的費(fèi)用是544639元，最后一個(gè)585349元是對(duì)全部的數(shù)據(jù)進(jìn)行會(huì)匯總。

相關(guān)代碼

主要是在group by 后面添加with rollup,此功能僅限于SQL數(shù)據(jù)庫(kù)的用戶(hù)，Access版不支持。

主查詢(xún)

Select
Payment.UserID as 工號(hào),
Payment.TrueName as 姓名,
Payment.PayMethod as 收費(fèi)方式,
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:校區(qū)} )
and Payment.DateAndTime >= {@StartDate:開(kāi)始日期}
and Payment.DateAndTime <= {@EndDate:結(jié)束日期}
Group by Payment.UserID,Payment.TrueName,Payment.PayMethod with rollup

子查詢(xún)

Select
Payment.PaymentID as 充值號(hào),
Payment.PaymentTypeName as 充值類(lèi)型,
Payment.PayMethod as 收費(fèi)方式,
Payment.DateAndTime as 交費(fèi)日期,
Payment.StudentID as 學(xué)號(hào),
Payment.StudentName as 學(xué)員,
Payment.PayMoney as 實(shí)收金額,
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