啊啊啊啊啊,昨天去海底捞面试,丢死人啦,因为我好久好久没用group by了.
面试问题:一张表,里面存放着各部门员工的应出勤天数,实际出勤天数,查出每个部门出勤率最高的三名员工及平均出勤率.

#mysql建表语句
create table `check`
(
    id            int        not null
        primary key,
    department_id varchar(2) null,
    user_id       int        null,
    should_days   int        null,
    actual_days   int        null
);

这里有个问题,如果某部门出勤率第三名有多个人,那可能选出来的就不止3个人,这个细节我没想到怎么去解决.

select c.department_id, c.user_id, c.actual_days/c.should_days as rate,t.rt
from `check` c
left join (select k.department_id, sum(k.actual_days)/sum(k.should_days) as rt from `check` k
group by k.department_id
) t on t.department_id = c.department_id
where
(
    select count(*) from `check` cc
    where c.department_id=cc.department_id
    and c.actual_days/c.should_days < cc.actual_days/cc.should_days
    )<3
order by c.department_id,rate desc;