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

由之冰巧发布于 2020-05-18 16:44:16

我正在尝试在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：列定义不明确

望_

2020-05-18 16:44:16

我认为您需要遵循以下原则：

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

2020-05-18 16:44:16

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;

之冰巧

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3228
注册排名
2179