我尝试过的
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 =“ Vacation假”具有requesttbl.max_allotment = 20,当在requestformtbl中插入新条目,其中requestformtbl.request_type =“ Vacation假”和requestformtbl.total_day = 5时,我想获得剩余的可用假
您可以GROUP BY
通过使用相关子查询来获取所需员工使用天数的总和,而无需使用a来获得所需的结果:
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;
注意我使用表别名使查询更具可读性。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句