SQL 面试题

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

# 数据库表设计:
qian_dao_table：考勤签到记录表，包含字段：id, user_id, department_id, time(考勤时间)
employee: 员工信息表，包含字段：id, name, department_id

# SQL 前提：
假设一周上班5天，上班时间为周一至周五。

# 题目：
1. 假如有个公司，员工每天上班需要签到一次，要列出每个员工上周的签到率，用一条sql怎么写
select
-- 员工ID
user_id,
-- 签到率
round(count(user_id)/5, 2) as rate
from qian_dao_table
where
FROM_UNIXTIME(time, '%Y-%m-%d') >= '2020-02-10'
and FROM_UNIXTIME(time, '%Y-%m-%d') <= '2020-02-14'
group by user_id


2. 假如公司分成若干个部门(员工从属于部门), 要列出每个部门上周的满勤率（员工上周签到率为100%才算满勤），用一条sql怎么写
select
-- 部门ID
A.department_id,
-- 部门满勤率
round(sum(if(B.`rate`=1, 1, 0))/count(A.department_id), 2) as rate_department
from employee as A
left join (select
    -- 员工ID
    user_id,
    -- 签到率
    round(count(user_id)/5, 2) as rate
    from qian_dao_table
    where
    FROM_UNIXTIME(time, '%Y-%m-%d') >= '2020-02-10'
    and FROM_UNIXTIME(time, '%Y-%m-%d') <= '2020-02-14'
    group by user_id
) as B
on A.id = B.user_id
group by A.department_id

3. 假如领导可以任意指定某一个部门周五也算正常休假，要列出每个部门上周的满勤率，用一条sql怎么写
# 假设设定部门 ID 等于 10 时，该部门周五也算正常休假
select
-- 部门ID
A.department_id,
-- 部门满勤率
round(sum(if(B.`rate`=1, 1, 0))/count(A.department_id), 2) as rate_department
from employee as A
left join (select
    -- 员工ID
    user_id,
    -- 签到率
    round(count(user_id)/if(department_id=10, 4, 5), 2) as rate
    from qian_dao_table
    where
    FROM_UNIXTIME(time, '%Y-%m-%d') >= '2020-02-10'
    and FROM_UNIXTIME(time, '%Y-%m-%d') <= '2020-02-14'
    group by user_id
) as B
on A.id = B.user_id
group by A.department_id

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

# 数据库设计
新增表 department_workday：部门工作日列表，包含字段：id, department_id, workday(工作日，使用整数0~6)

# sql
select
-- 部门ID
A.department_id,
-- 部门满勤率
round(sum(if(B.`rate`>=1, 1, 0))/count(A.department_id), 2) as rate_department
from employee as A
left join (select
    -- 员工ID
    user_id,
    -- 签到率
    round(count(user_id) / (
        select
        -- 计算该用户在某段时间内的工作日数量
        count(1) as workday_num
        from employee as C
        left join department_workday as D on C.department_id = D.department_id
        where
        C.id = qian_dao_table.user_id
        and D.workday in (date_format('2020-02-12', '%w'), date_format('2020-02-13', '%w'), date_format('2020-02-14', '%w'), date_format('2020-02-15', '%w'))
    ), 2) as rate
    from qian_dao_table
    where
    FROM_UNIXTIME(time, '%Y-%m-%d') >= '2020-02-12'
    and FROM_UNIXTIME(time, '%Y-%m-%d') <= '2020-02-15'
    group by user_id
) as B
on A.id = B.user_id
group by A.department_id

# 注意
- 我这条sql会存在一个问题，如果用户在该部门的非工作日打卡，就会导致数据统计出现误差。
- 像这种复杂逻辑，一般用后端代码写会更好。