你是数据分析方面的专家,请根据以下数据表信息回答问题: create table department ( departmentid int primary key…
- User
你是数据分析方面的专家,请根据以下数据表信息回答问题: create table department ( department_id int primary key auto_increment comment '部门编码', name varchar(50) comment '部门名称', department_date date comment '成立日期', description varchar(100) comment '部门描述' ) comment='部门表'; create table employees ( employee_id int primary key auto_increment comment '员工编码', dep_id int comment '部门id', name varchar(50) comment '姓名', gender enum('12', '14') comment '性别,12为男性、14为女性', hire_date date comment '入职日期', position varchar(50) comment '岗位,岗位包括销售、技术、售后、客服等', monthly_salary decimal(10, 2) comment '月薪资', contact_number varchar(20) comment '联系电话', birthday date comment '生日', origin varchar(50) comment '籍贯', birth_city varchar(50) comment '出生城市', birth_province varchar(50) comment '出生省份', marriage_status boolean comment '婚姻状态', resignation_date date comment '离职日期,在职员工该字段为空', foreign key (dep_id) references department (department_id) ) comment='员工信息'; create table attendance ( employee_id int comment '员工编码', date date comment '考勤日期', arrive_time time comment '上班时间', leave_time time comment '下班时间', is_absent boolean comment '是否请假', primary key (employee_id, date) ) comment='考勤信息'; create table employee_leave_record ( id int primary key, employee_id int not null, apply_time date not null, approval_status int comment '审核状态,1为通过、2为未通过、0为未审核', leave_type int comment '请假类型,0为婚假、1:产假 2:探亲 3:其他', leave_description varchar(255) comment '请假描述', holiday_start_time date comment '假期开始时间', holiday_end_time date comment '假期结束时间', foreign key (employee_id) references employees (employee_id) ) comment='员工请假记录'; create table salary_increase_record ( id int primary key, employee_id int not null, original_salary decimal(10, 2) not null, new_salary decimal(10, 2) not null, raise_date date not null, foreign key (employee_id) references employees (employee_id) ) comment='薪资增长记录表';
以上是一些MYSQL数据库表的定义,请回答问题:电话号码的5到8位为号段标志,其规则如下:以135开头的为号段A, 136开头的为 号段B 125开头的为号段C,统计各部门各号段标志员工人数、平均年龄、平均在职年限、平均薪资
Assistant
根据您提供的数据库表定义,以下是对问题的解答:
首先,我们需要计算每个员工的年龄和在职年限。可以使用以下查询来计算员工的年龄:
SELECT employee_id, YEAR(CURRENT_DATE) - YEAR(birthday) - IF(DAYOFYEAR(CURRENT_DATE) < DAYOFYEAR(birthday), 1, 0) AS age FROM employees WHERE resignation_date IS NULL;该查询使用了 IF 函数来判断当前日期是否在员工生日之前,以计算员工的年龄。
接下来,我们可以使用以下查询来计算每个员工的在职年限:
SELECT employee_id, YEAR(CURRENT_DATE) - YEAR(hire_date) - IF(DAYOFYEAR(CURRENT_DATE) < DAYOFYEAR(hire_date), 1, 0) AS years_of_service FROM employees WHERE resignation_date IS NULL;该查询也使用了 IF 函数来计算员工的在职年限。
然后,我们可以使用以下查询来按照部门和号段标志分组,并计算员工人数、平均年龄、平均在职年限和平均薪资:
SELECT d.name AS department, CASE WHEN SUBSTR(e.contact_number, 5, 3) = '135' THEN 'A' WHEN SUBSTR(e.contact_number, 5, 3) = '136' THEN 'B' WHEN SUBSTR(e.contact_number, 5, 3) = '125' THEN 'C' ELSE 'Other' END AS phone_segment, COUNT(*) AS employee_count, AVG(age) AS avg_age, AVG(years_of_service) AS avg_years_of_service, AVG(monthly_salary) AS avg_salary FROM employees e JOIN department d ON e.dep_id = d.department_id JOIN ( SELECT employee_id, YEAR(CURRENT_DATE) - YEAR(birthday) - IF(DAYOFYEAR(CURRENT_DATE) < DAYOFYEAR(birthday), 1, 0) AS age FROM employees WHERE resignation_date IS NULL ) e1 ON e.employee_id = e1.employee_id JOIN ( SELECT employee_id, YEAR(CURRENT_DATE) - YEAR(hire_date) - IF(DAYOFYEAR(CURRENT_DATE) < DAYOFYEAR(hire_date), 1, 0) AS years_of_service FROM employees WHERE resignation_date IS NULL ) e2 ON e.employee_id = e2.employee_id WHERE e.resignation_date IS NULL GROUP BY d.name, CASE WHEN SUBSTR(e.contact_number, 5, 3) = '135' THEN 'A' WHEN SUBSTR(e.contact_number, 5, 3) = '136' THEN 'B' WHEN SUBSTR(e.contact_number, 5, 3) = '125' THEN 'C' ELSE 'Other' END;该查询首先使用子查询来计算员工的年龄和在职年限,然后使用 JOIN 来连接员工表、部门表以及子查询结果。最后,使
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.