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

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