注册    登录
关注
Py学习  »  DATABASE

mysql求和列总数

Arnaiz • 5 年前 • 1490 次点击  

以学校为例,我必须在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

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/39944
Reply
 
1490 次点击  
文章 [ 1 ]  |  最新文章 5 年前
Barmar
Reply   •   1 楼
Barmar    6 年前

把 UNION 进入子查询。在主查询中,将所有计数和工资总额相加,然后使用 WITH ROLLUP 得到总数。

你不需要 dailySalary 在 GROUP BY 子句,因为它在功能上依赖于id。 

SELECT name AS Name, SUM(count) AS `Shows Attended in March`, SUM(totalSalary) AS `Total Salary`
FROM (
    SELECT Coach.name, COUNT(*) AS count, SUM(dailySalary) AS totalSalary
    FROM Coach
    JOIN CoachInShow ON Coach.idCoach=CoachInShow.idCoach
    JOIN TVShow ON TVShow.idShow = CoachInShow.idShow 
    WHERE monthname(dateOfShow)='March'
    GROUP BY Coach.idCoach

    UNION

    SELECT Participant.name, COUNT(*) AS count, SUM(dailySalary) AS totalSalary
    FROM Participant
    JOIN Contender ON Participant.idContender = Contender.idContender 
    JOIN ContenderInShow ON Contender.idContender = ContenderInShow.idContender
    JOIN TVShow ON ContenderInShow.idShow = TVShow.idShow
    WHERE monthname(dateOfShow)='March'
    GROUP BY Participant.idParticipant
) AS x
GROUP BY Name
WITH ROLLUP

DEMO

登录后回复
关于   移动版
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号