-
统计不同流程当月发起的实例数
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT
d.`
name
`,
count
(*)
FROM
t_bpm_process_execution e
LEFT
JOIN
t_bpm_process_define d
ON
d.id = e.processDefineId
WHERE
e.createTime
BETWEEN
'2016-05-23'
AND
'2016-06-24'
GROUP
BY
e.processDefineId
|
2.统计节点次数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELECT
*
FROM
(
SELECT
activityName,
count
(*)
as
count
,
define.processName
FROM
t_bpm_process_task task
LEFT
JOIN
t_bpm_process_execution exe
ON
task.processExecutionId = exe.id
LEFT
JOIN
t_bpm_process_define define
ON
define.id = exe.processDefineId
WHERE
task.createTime
BETWEEN
'2016-05-23'
AND
'2016-06-24'
AND
activityName
NOT
LIKE
'%start%'
AND
activityName
NOT
LIKE
'%重新填写%'
GROUP
BY
activityName,define.processName
) e
order
by
e.processName
asc
,e.
count
desc
|
3.按流程统计节点出现的总次数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELECT
sum
(`
count
`),e.processName
FROM
(
SELECT
activityName,
count
(*)
as
count
,
define.processName
FROM
t_bpm_process_task task
LEFT
JOIN
t_bpm_process_execution exe
ON
task.processExecutionId = exe.id
LEFT
JOIN
t_bpm_process_define define
ON
define.id = exe.processDefineId
WHERE
task.createTime
BETWEEN
'2016-05-23'
AND
'2016-06-24'
AND
activityName
NOT
LIKE
'%start%'
AND
activityName
NOT
LIKE
'%重新填写%'
GROUP
BY
activityName,define.processName
) e
group
by
e.processName
|
4.为createtime建立索引
|
1
2
3
|
create
index
idx_t_bpm_process_task_createTime
on
t_bpm_process_task(createTime)
;
|
5.将日期设置为半年前
|
1
2
3
4
5
6
7
8
9
10
|
update
jbpm4_hist_task
set
CREATE_ = DATE_SUB(CREATE_,INTERVAL 6
MONTH
),
END_ = DATE_SUB(END_,INTERVAL 6
MONTH
)
where
EXECUTION_ =
'文件制定变更申请单.16645623'
;;
update
jbpm4_hist_actinst
update
jbpm4_hist_actinst
set
start_ = DATE_SUB(START_,INTERVAL 6
MONTH
),
END_ = DATE_SUB(END_,INTERVAL 6
MONTH
)
where
execution_ =
'文件制定变更申请单.16645623'
;;
update
jbpm4_hist_procinst
set
START_ = DATE_SUB(START_,INTERVAL 6
MONTH
),
END_ = DATE_SUB(END_,INTERVAL 6
MONTH
)
where
ID_ =
'文件制定变更申请单.16645623'
;;
|
6.工作经历表工作时间填写报错
工作时间的类型被定义为tinyint，该类型可表示范围-128~127之内的整数，填写127以上则报错，修改sql为：
|
1
|
alter
table
t_per_workexperience
modify
column
correlationSeniority
INT
(3)
|
7.更新签证表单报数据库约束错误
更新签证表会下发alter table语句，护照持有人的出生日期被定义为timestamp，表中已存在一名出生日期为1960年的总裁，而timestamp有其数据范围为1970-01-01 8:00:01UTC～2038/01/19，所以报错，修改类型为datetime后解决。
数据库中的 date datetime timestamp的区别
8.查询所有的trigger及其定义
|
1
2
3
4
5
6
7
8
9
10
11
|
select
*
from
information_schema.`TRIGGERS` ;
use oa_prod ;
show
create
trigger
trg_staffinfo_delete ;
show
create
trigger
trg_staffinfo_delete ;
CREATE
DEFINER=`mei_oa`@`%`
TRIGGER
`trg_staffinfo_insert`
AFTER
INSERT
ON
`t_per_staffinfo`
FOR
EACH ROW
BEGIN
INSERT
INTO
t_att_userorg_sync_mark(id,relateID,relateName,relateType,synchro,syncType,domain,createtime)
VALUES
(UUID(),new.staffId,new.staffName,1,
'N'
,1,
'C6'
,NOW());
END
|
本文转自 gaochaojs 51CTO博客，原文链接：http://blog.51cto.com/jncumter/1831291
，如需转载请自行联系原作者
