mysql 查询拓展 触发器 交叉表 存储过程_MySQL

bitsCN.com

BEGIN-- 管理员使用 用于快速创建人员的基本数据工龄与系数	DECLARE done INT DEFAULT 0;DECLARE usewy int;DECLARE user int;DECLARE jobs int ;DECLARE jobxs FLOAT;DECLARE users CURSOR	FOR SELECT user_id FROM 小野_sys_user WHERE department_id   1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;SET done=0;OPEN users;	REPEAT  	FETCH users INTO user;IF done=0 THENSELECT job_id FROM 小野_sys_user WHERE user_id =user INTO jobs;SELECT sgmodulus FROM 小野_sys_job WHERE job_id=jobs INTO jobxs;SELECT YEAR(CURDATE())-YEAR(workyear) FROM 小野_sys_user WHERE user_id=user INTO usewy;INSERT INTO 小野_year_user(user_id,work_date,in_date,xis)VALUES(user,usewy,CURDATE(),jobxs);end if;	UNTIL done END REPEAT;CLOSE users;ENDSHOW TRIGGERS;DROP TRIGGER insertUserDROP TRIGGER deleteUser;-- 随时更新部门人数DROP TRIGGER updateUser;CREATE TRIGGER insertUser BEFORE  insert on 小野_sys_user for each row BEGIN UPDATE 小野_sys_department SET persons = (SELECT COUNT(*) FROM 小野_sys_user as u  WHERE u.department_id = new.department_id AND u.roler_id not IN (35)) WHERE department_id = new.department_id;ENDCREATE TRIGGER deleteUser BEFORE DELETE on 小野_sys_user for each row BEGIN UPDATE 小野_sys_department SET persons = (SELECT COUNT(*) FROM 小野_sys_user as u  WHERE u.department_id = old.department_id AND u.roler_id not IN (35)) WHERE department_id = old.department_id;ENDCREATE TRIGGER updateUser BEFORE UPDATE on 小野_sys_user for each row BEGIN UPDATE 小野_sys_department SET persons = (SELECT COUNT(*) FROM 小野_sys_user as u  WHERE u.department_id = new.department_id AND u.roler_id not IN (35)) WHERE department_id = new.department_id;UPDATE 小野_sys_department SET persons = (SELECT COUNT(*) FROM 小野_sys_user as u  WHERE u.department_id = old.department_id AND u.roler_id not IN (35)) WHERE department_id = old.department_id;ENDSELECT s.dept_id,FORMAT(SUM(IF(flag=1,score,0)),1) AS cgkh,FORMAT(SUM(IF(flag=2,score,0)),1) AS zxkh,FORMAT(SUM(IF(flag in (1,2),score,0)),1) AS sumkh,d.department_name AS dept_ids FROM 小野_score_dept AS s,小野_sys_department AS d WHERE DATE_FORMAT(deal_date,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(),interval 1 MONTH),'%Y-%m') AND d.department_id = s.dept_id GROUP BY s.dept_id ORDER BY s.dept_id ASC  SELECT d.department_name,FORMAT(SUM(IF(s.flag = 0 AND s.dept_id =  d.department_id ,score,0))+10,1) AS zdscore,FORMAT(SUM(IF(s.flag in(1,2) AND s.dept_id =  d.department_id ,score,0))+50,1) AS gjscore,FORMAT(SUM(IF(s.flag = 3 AND s.dept_id =  d.department_id ,score,0)),1) AS ggscore,FORMAT(SUM(IF(s.flag = 4 AND s.dept_id =  d.department_id ,score,0)),1) AS mzscore,FORMAT(SUM(IF(s.flag in(0,1,2,3,4 )AND s.dept_id =  d.department_id ,score,0)),1) AS sumsscore FROM 小野_sys_department as d, 小野_score_dept as s WHERE d.department_id  1 AND DATE_FORMAT(s.deal_date,'%Y-%m')= DATE_FORMAT(DATE_SUB(CURDATE(),interval 1 MONTH),'%Y-%m') GROUP BY d.department_id ORDER BY d.department_id 


bitsCN.com