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

<pre name="code" class="sql">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 THEN
SELECT 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;
END

SHOW TRIGGERS;
DROP TRIGGER insertUser
DROP 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;
END
CREATE 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;
END
CREATE 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;
END

SELECT 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