sql1、员工表emp工号empid 姓名name 手机号phone 部门dept 职位 job 工龄jobyear 级别 level 家庭住址 addr部门有：行政、开发、测试、销售、财务、后勤职位有： 总经理 总监 组长 员工 2、请假表holiday开始时间begintime 结束时间endtimd 天数days 原因reason 员工工号empid时间都是精确到年月日天数精确到05天1 请

1、创建两张表

CREATE TABLE emp ( empid INT PRIMARY KEY, name VARCHAR(20), phone VARCHAR(20), dept VARCHAR(20), job VARCHAR(20), jobyear INT, level INT, addr VARCHAR(50) );

CREATE TABLE holiday ( id INT PRIMARY KEY, begintime DATE, endtime DATE, days FLOAT(1), reason VARCHAR(20), empid INT, FOREIGN KEY (empid) REFERENCES emp(empid) );

2、向emp中插入100条数据

INSERT INTO emp (empid, name, phone, dept, job, jobyear, level, addr) VALUES (1, '张三', '13811111111', '行政', '总经理', 10, 5, '北京市朝阳区'), (2, '李四', '13922222222', '开发', '总监', 8, 4, '北京市海淀区'), (3, '王五', '13633333333', '测试', '经理', 6, 3, '北京市丰台区'), ... (100, '赵六', '13999999999', '财务', '员工', 1, 1, '北京市东城区');

3、向holiday中添加100条数据

INSERT INTO holiday (id, begintime, endtime, days, reason, empid) VALUES (1, '2021-01-01', '2021-01-07', 5, '事假', 1), (2, '2021-01-02', '2021-01-03', 1, '病假', 2), (3, '2021-01-05', '2021-01-08', 2.5, '年假', 3), ... (100, '2021-09-29', '2021-09-30', 0.5, '事假', 100);

4、9月份请病假的人数

SELECT COUNT(*) FROM holiday WHERE reason = '病假' AND MONTH(begintime) = 9;

5、开发部9月份请假的人名和电话号码

SELECT emp.name, emp.phone FROM emp JOIN holiday ON emp.empid = holiday.empid WHERE emp.dept = '开发' AND MONTH(holiday.begintime) = 9;

6、每个人的请假次数

SELECT emp.empid, emp.name, COUNT(*) AS holiday_times FROM emp LEFT JOIN holiday ON emp.empid = holiday.empid GROUP BY emp.empid, emp.name;

7、统计每个部门有多少人

SELECT dept, COUNT(*) AS emp_count FROM emp GROUP BY dept;

8、统计每个部门请假次数

SELECT emp.dept, COUNT(*) AS holiday_times FROM emp LEFT JOIN holiday ON emp.empid = holiday.empid GROUP BY emp.dept;

9、从来没请过假的员工编号和人名

-- 方法1： 不匹配的记录 外连接 SELECT emp.empid, emp.name FROM emp LEFT JOIN holiday ON emp.empid = holiday.empid WHERE holiday.id IS NULL;

-- 方法2：子查询 not in SELECT emp.empid, emp.name FROM emp WHERE emp.empid NOT IN (SELECT empid FROM holiday);

10、哪个类型的请假次数最多【病假？年假？】

SELECT reason, COUNT(*) AS holiday_times FROM holiday GROUP BY reason ORDER BY holiday_times DESC LIMIT 1;

11、打印每个员工的累加请假时间，按累加时间从高到低排序格式为：工号 姓名 累计时间

SELECT emp.empid, emp.name, SUM(holiday.days) AS total_days FROM emp LEFT JOIN holiday ON emp.empid = holiday.empid GROUP BY emp.empid, emp.name ORDER BY total_days DESC;

12、请病假次数最多的月份是几月，有多少次

SELECT MONTH(begintime) AS month, COUNT(*) AS holiday_times FROM holiday WHERE reason = '病假' GROUP BY month ORDER BY holiday_times DESC LIMIT 1