title: 泛微 E9 运维核心 SQL 语句库 created: 2026-04-21 updated: 2026-04-21 type: reference tags: [sql, db, ops, e9, maintenance] related: [oa/ecology9-schema/index.md]
泛微 E9 运维核心 SQL 语句库
⚠️ 警告：执行
UPDATE/DELETE前请务必备份数据！本表仅列出SELECT查询语句。
1. 流程运行监控 (Workflow)
1.1 查询当前卡在某节点的所有流程
SELECT
r.requestid,
r.requestname,
r.workflowid,
r.creater,
h.lastname AS creater_name,
l.nodeid,
n.nodename,
l.receivedate,
l.receivetime
FROM workflow_requestbase r
JOIN workflow_currentoperator l ON r.requestid = l.requestid
JOIN workflow_nodebase n ON l.nodeid = n.id
JOIN hrmresource h ON r.creater = h.id
WHERE l.isremark = '0' -- '0' 表示待处理
AND l.userid = [用户 ID]; -- 可选：指定查询某人的待办
1.2 查询超时未处理流程 (SLA 监控)
SELECT
r.requestid,
r.requestname,
l.operatedate,
l.operatetime,
DATEDIFF(day, l.operatedate, GETDATE()) as delay_days -- SQL Server 语法
FROM workflow_requestbase r
JOIN workflow_currentoperator l ON r.requestid = l.requestid
WHERE l.isremark = '0'
AND l.operatedate < DATEADD(day, -3, GETDATE()) -- 超过 3 天未处理
ORDER BY l.operatedate ASC;
1.3 统计某段时间内的流程发起量
SELECT
w.workflowname,
COUNT(*) as request_count
FROM workflow_requestbase r
JOIN workflow_base w ON r.workflowid = w.id
WHERE r.createrdate BETWEEN '2026-04-01' AND '2026-04-30'
GROUP BY w.workflowname
ORDER BY request_count DESC;
2. 组织与权限管理 (HR & Permission)
026-04-30' GROUP BY w.workflowname ORDER BY request_count DESC;
## 2. 组织与权限管理 (HR & Permission)### 2.1 查询某人的所有角色和部门
```sql
SELECT
h.loginid,
h.lastname,
d.departmentname,
r.rolename
FROM hrmresource h
LEFT JOIN hrmdepartment d ON h.departmentid = d.id
LEFT JOIN hrmrolemembers rm ON h.id = rm.resourceid
LEFT JOIN hrmroles r ON rm.roleid = r.id
WHERE h.loginid = '[账号]';
2.2 查找长期未登录账号 (沉睡账号)
SELECT
loginid,
lastname,
lastlogindate,
status
FROM hrmresource
WHERE status IN ('0', '1', '2', '3') -- 正常/停用等状态
AND (lastlogindate IS NULL OR lastlogindate < '2025-01-01')
ORDER BY lastlogindate ASC;
3. 表单数据查询 (Form Data)
注：表单数据表名通常为 formtable_main_XX，其中 XX 为表单 ID。
3.1 查询特定表单的所有记录
SELECT
requestid,
field1, -- 替换为实际字段名
field2
FROM formtable_main_123
WHERE requestid IN (SELECT requestid FROM workflow_requestbase WHERE workflowid = 10);