MySQL存储过程（insert数据）：insert into table(col1,colu2,colu3) select col1,col2,col3 from table2 where ...；

由于业务需求，需要重新建一张表用来统计每日在职人数及入离职情况，需要根据已存在的记录生成日统计记录。
需要通过日期生成记录，只想统计最近几个月的记录所以使用存储过程（本方法产生的日在职记录在职人数是不准确的，仅在建表之后使用一次产生足够的测试数据）。

使用到的函数：

日期格式化：DATE_FORMAT(date,format)
获取间隔日期：DATE_SUB(NOW(),INTERVAL initNum DAY) ---获取initNum天前的数据
生成主键id：MD5(UUID())

#`nums`代表执行存储过程需传入int类型的形参
CREATE DEFINER=`root`@`localhost` PROCEDURE `createRecord`(IN `nums` int)
BEGIN
	#Routine body goes here...
declare initNum int default 0;
DECLARE currentDay char(13);
		WHILE initNum<=nums DO
			SET initNum = initNum +1;
			SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL initNum DAY),'%Y-%m-%d') INTO currentDay;
			INSERT INTO s_emprecorddaily_t(ID,DEPARTID,QUIT_COUNT,ENTRY_COUNT,REGISTER_COUNT,QUIT_TIMES,ENTRY_TIMES,EMPLOYEES_COUNT,ZS_COUNT,ZB_COUNT,CREATE_DATE)
				SELECT
				#REPLACE(UUID(),'-','')导致主键重复；使用MD5(UUID()); 别名赋不赋都行，与insert对应就行
					MD5(UUID()) as ID,
					re.DEPARTID as DEPARTID,
					re.quitCount AS QUIT_COUNT,
					re.entryCount AS ENTRY_COUNT,
					re.registerCount AS REGISTER_COUNT,
					re.quitTimes AS QUIT_TIMES,
					re.entryTimes AS ENTRY_TIMES,
					em.EMPLOYEES_COUNT AS EMPLOYEES_COUNT,
					em.zsCount AS ZS_COUNT,
					em.zbCount AS ZB_COUNT,
					currentDay as createDate
					FROM
					#获取记录表相关数据
							(SELECT
								ss.DEPARTID as departId,
								SUM(IF(ss.QUIT_TIMES>0,1,0)) AS quitCount,
								SUM(IF(ss.ENTRY_TIMES>0,1,0)) AS entryCount,
								SUM(IF(ss.ENTRY_TIMES>ss.QUIT_TIMES,1,0)) AS registerCount,
								SUM(ss.QUIT_TIMES) AS quitTimes,
								SUM(ss.ENTRY_TIMES) AS entryTimes
								FROM
								(SELECT
									SUM(IF(RECORDTYPE=0,1,0)) AS ENTRY_TIMES,
									SUM(IF(RECORDTYPE=1,1,0)) AS QUIT_TIMES,
									SUM(case when RECORDWAY='0' and RECORDTYPE='0' then 1
													 when RECORDWAY='3' and RECORDTYPE='0' then 1 else 0 end) as REGISTER_COUNT,
									EMPID,DEPARTID
							FROM s_emprecord_t
							where  DATE_FORMAT(CREATETIME,'%Y-%m-%d')=STR_TO_DATE(currentDay,'%Y-%m-%d')
							GROUP BY EMPID,DEPARTID
							ORDER BY EMPID)ss
					LEFT JOIN s_department_t d ON d.ID=ss.DEPARTID
        WHERE d.DEPTSTATUS='2'
        GROUP BY ss.DEPARTID)re,
        #记录表无人员在职情况，所以需要重新获取
							(select
        sum(case when eo.STYPE='1' then 1 else 0 end) as zbCount,
        sum(case when eo.STYPE='0' then 1 else 0 end) as zsCount,
        count(*) as EMPLOYEES_COUNT,
				eo.DEPARTID as departId
        from s_EMPLOYEE_T e
        left JOIN S_EMPLOYEEOBJECT_T eo ON e.id = eo.userid
        left JOIN s_department_t d on d.id = eo.DEPARTID
				WHERE
					IFNULL(d.DEPARTNAME,'NUll')!='NUll'
					and IFNULL(eo.STYPE,'NUll')!='NUll'
					and eo.state = '0'
					and d.DEPTSTATUS= '2'
        GROUP BY d.DEPARTNAME)em 
				WHERE em.DEPARTID = re.DEPARTID;
		END WHILE;

END