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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql查询拓展触发器交叉表存储过程_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2929字，纯文字阅读大概需要5分钟。

内容图文

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


           
                
                                                

内容总结

以上是互联网集市为您收集整理的mysql查询拓展触发器交叉表存储过程_MySQL全部内容，希望文章能够帮你解决mysql查询拓展触发器交叉表存储过程_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端