mysql - 从另一张表的列中减去一列的总和

我尝试过的

SELECT requestformtbl.employee_name, requestformtbl.request_type, requestformtbl.total_day,
                requestformtbl.request_status, requestformtbl.admin_remark, requestformtbl.confirmed_by, requestformtbl.date_confirmed, requesttbl.max_allotment,
                (requesttbl.max_allotment - sum(requestformtbl.total_day)) as Available from requestformtbl inner join requesttbl on
                requestformtbl.request_type = requesttbl.request_type;

错误：选择列表中的列'requestformtbl.employee_name'无效，因为该列未包含在聚合函数或GROUP BY子句中。

如果requestttbl.request_type =“假期”具有requesttbl.max_allotment = 20，
当在requestformtbl中插入新条目并带有requestformtbl.request_type =“ Vacation Leave”和requestformtbl.total_day = 5时
我想得到剩余的假期

最佳答案

您可以通过使用相关子查询来获取所需雇员的天数之和，而无需使用GROUP BY来获得所需的结果：

SELECT rft.employee_name,
       rft.request_type,
       rft.total_day,
       rft.request_status,
       rft.admin_remark,
       rft.confirmed_by,
       rft.date_confirmed,
       rt.max_allotment,
       rt.max_allotment - (select sum(total_day)
                           from requestformtbl rft2
                           where rft2.employee_name = rft.employee_name) as Available
from requestformtbl rft
inner join requesttbl rt on rft.request_type = rt.request_type;

注意我使用表别名使查询更具可读性。

关于mysql - 从另一张表的列中减去一列的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59223859/