|
CREATE VIEW train_need_view AS SELECT row_number () OVER (ORDER BY b.emoployee_name) AS id, b.emoployee_name, b.emoployee_id, b.id_card, b.course_id, b.course_name, b.train_hours, b.reason, b.organ_id, b.organ_name, b.department_id FROM ( SELECT a.emoployee_name, a.emoployee_id, a.id_card, a.course_id, a.course_name, a.train_hours, a.reason, a.organ_id, a.organ_name, a.department_id -- a.parent_id -- a.department_type, --a.department_name FROM ( SELECT sys_employee.name AS emoployee_name, sys_employee.id AS emoployee_id, sys_employee.id_card, train_need.course_id, course.course_name, course.train_hours, train_need.reason, sys_organ.id AS organ_id, sys_organ.organ_name, sys_employee.department_id -- sys_department.parent_id, -- sys_department.type AS department_type, -- sys_department.department_name FROM train_need, sys_employee, sys_organ, course --sys_department WHERE train_need.employee_id = sys_employee.id AND sys_employee.organ_id = sys_organ.id AND course.id = train_need.course_id --AND sys_employee.department_id=sys_department.id UNION ALL SELECT sys_employee.name AS emoployee_name, sys_employee.id AS emoployee_id, sys_employee.id_card, break_rule_education.course_id, course.course_name, course.train_hours, '违章教育' AS reason, sys_organ.id AS organ_id, sys_organ.organ_name, sys_employee.department_id -- sys_department.parent_id, -- sys_department.type AS department_type, -- sys_department.department_name FROM break_rule_education, sys_employee, sys_organ, course -- sys_department WHERE break_rule_education.employee_id = sys_employee.id AND sys_employee.organ_id = sys_organ.id AND break_rule_education.course_id = course.id -- AND sys_employee.department_id = sys_department.id UNION ALL SELECT sys_employee.name AS emoployee_name, sys_employee.id AS emoployee_id, sys_employee.id_card, NULL AS course_id, NULL AS course_name, NULL AS train_hours, '持续培训覆盖率低' AS reason, sys_organ.id AS organ_id, sys_organ.organ_name, sys_employee.department_id --sys_department.parent_id, -- sys_department.type AS department_type, --sys_department.department_name FROM sys_employee, sys_organ -- sys_department WHERE sys_employee.organ_id = sys_organ.id AND sys_employee.continue_train_rate < 0.005 --AND sys_employee.department_id = sys_department.id UNION ALL SELECT sys_employee.name AS emoployee_name, sys_employee.id AS emoployee_id, sys_employee.id_card, course.id AS course_id, course.course_name AS course_name, course.train_hours, '培训覆盖率低' AS reason, sys_organ.id AS organ_id, sys_organ.organ_name, sys_employee.department_id -- sys_department.parent_id, --sys_department.type AS department_type, --sys_department.department_name FROM sys_employee, course_position, sys_organ, course -- sys_department WHERE sys_employee.organ_id = sys_organ.id AND sys_employee.train_rate < 1 AND course_position.course_id = course.id AND course.disable = 0 AND course_position.disable = 0 AND sys_employee.disable = 0 AND sys_employee.position_id = course_position.position_id --AND sys_employee.department_id = sys_department.id AND NOT EXISTS ( SELECT * --train_record.course_id--,train_record.employee_id FROM train_record WHERE train_record.employee_id = sys_employee.id AND train_record.course_id = course_position.course_id ) GROUP BY course.id, course.course_name, course.train_hours, sys_employee.id, sys_employee.name, sys_employee.id_card, sys_organ.id, sys_organ.organ_name, sys_employee.department_id -- sys_department.parent_id, -- sys_department.type, -- sys_department.department_name UNION ALL SELECT sys_employee.name AS emoployee_name, sys_employee.id AS emoployee_id, sys_employee.id_card, course.id AS course_id, course.course_name AS course_name, course.train_hours, '培训覆盖率低' AS reason, sys_organ.id AS organ_id, sys_organ.organ_name, sys_employee.department_id --sys_department.parent_id, --sys_department.type AS department_type, --sys_department.department_name FROM sys_employee, course_work_type, sys_organ, course -- sys_department WHERE sys_employee.organ_id = sys_organ.id AND sys_employee.train_rate < 1 AND course_work_type.course_id = course.id AND course.disable = 0 AND course_work_type.disable = 0 AND sys_employee.disable = 0 AND sys_employee.work_type_id = course_work_type.work_type_id --AND sys_employee.department_id = sys_department.id AND NOT EXISTS ( SELECT * --train_record.course_id--,train_record.employee_id FROM train_record WHERE train_record.employee_id = sys_employee.id AND train_record.course_id = course_work_type.course_id ) GROUP BY course.id, course.course_name, course.train_hours, sys_employee.id, sys_employee.name, sys_employee.id_card, sys_organ.id, sys_organ.organ_name, sys_employee.department_id --sys_department.parent_id, --sys_department.type, --sys_department.department_name ) AS a GROUP BY a.emoployee_name, a.emoployee_id, a.id_card, a.course_id, a.course_name, a.train_hours, a.reason, a.organ_id, a.organ_name, a.department_id -- a.parent_id, --a.department_type, -- a.department_name ) AS b |
|
|
