我正在尝试在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：列定义不明确
我认为您需要遵循以下原则：
select s.staffid, count(h.staffid) no_holidays
from staff s
left join holiday h
on h.staffid = a.staffid
and h.holiday_date >= current_date - interval '1' year
group by s.staffid;
The left join brings corresponding records in holiday, while allowing staff that did not take any holiday. I added a filter on the holiday_date, that is described in the question. You can then aggregate and count.
You could also express this with a lateral join or a subquery, which make it easier to display more columns from staff:
select
s.*,
(
select count(*)
from holiday h
where h.staffid = a.staffid and h.holiday_date >= current_date - interval '1' year
) no_holidays
from staff s
The following query should work, you are missing holiday table in join condition. Also if both the staffid's are same then use one of them only.
select
staff.staffid,
COUNT(*)
from staff
inner join holiday
on staff.staffid = holiday.staffid
group by
staff.staffid;