我正在尝试在SQL中创建一个视图,该视图将检查工作人员一年中从假期表中获得了多少假期(例如,将Staffid 1输入到假期表中4次,因此它将显示staffid:1 holidaycount :4)
我试图这样写:
select
holiday.staffid,
staff.staffid,
COUNT(*)
from
staff
inner join staff on staff.staffid = holiday.staffid
group by staff.staffid;
这给了我错误:ORA-00918:列定义不明确
我认为您需要遵循以下原则:
The
left join
brings corresponding records inholiday
, while allowing staff that did not take any holiday. I added a filter on theholiday_date
, that is described in the question. You can then aggregate andcount
.You could also express this with a lateral join or a subquery, which make it easier to display more columns from
staff
:The following query should work, you are missing
holiday
table injoin
condition. Also if both thestaffid's
are same then use one of them only.