计算用户在另一个具有内部联接的表中具有的条目数

由 发布于 oraclesql
 收藏

我正在尝试在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

  • iullam
    iullam 回复

    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;