以学校为例,我必须在3月份得到教练和参与者的总工资(见下文),然后我必须求和才能得到所有员工3月份的总工资,我只想在总工资栏的末尾加上。
这就是我目前所拥有的:
(SELECT Coach.name AS Name, COUNT(*) AS 'Shows Attended In March',
dailySalary AS 'Daily Salary', sum(dailySalary) AS 'Total Salary'
FROM Coach, TVShow, CoachInShow
WHERE monthname(dateOfShow)='March' AND
Coach.idCoach=CoachInShow.idCoach AND TVShow.idShow =
CoachInShow.idShow
GROUP BY Coach.name, Coach.dailySalary)
UNION
(SELECT Participant.name AS Name, COUNT(*) AS 'Shows Attended In
March', dailySalary AS 'Daily Salary', sum(dailySalary) AS 'Total
Salary'
FROM Participant, TVShow, Contender, ContenderInShow
WHERE monthname(dateOfShow)='March' AND Participant.idContender =
Contender.idContender AND Contender.idContender =
ContenderInShow.idContender AND ContenderInShow.idShow = TVShow.idShow
GROUP BY Participant.name, Participant.dailySalary);
我试着使用group by和rollback来处理整件事,但它不只是totalsalary列的总和。我花了一段时间在这上面,有点难堪。
我在这里粘贴了我正在使用的数据:
https://www.db-fiddle.com/f/gPKVQrZCMkvHUqViAUzCqZ/0
http://sqlfiddle.com/#!9/535f6d/1
