<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
mysql 查询拓展 触发器 交叉表 存储过程
最新推荐文章于 2021-01-27 19:49:45 发布