泛微OA-SQL主从表 报表方式显示

SELECT
(CASE WHEN
ROW_NUMBER() OVER (PARTITION BY a.requestid ORDER BY b.LOGID) = 1 THEN a.requestid
ELSE '' END) AS "请求ID",
(CASE WHEN
ROW_NUMBER() OVER (PARTITION BY a.requestid ORDER BY b.LOGID) = 1 THEN a.requestnamehtmlnew
ELSE '' END) AS "请求标题",
(CASE WHEN
ROW_NUMBER() OVER (PARTITION BY a.requestid ORDER BY b.LOGID) = 1 THEN
(select (CASE WHEN lastname like '%~`~`7%' THEN SUBSTRING(SUBSTRING(lastname,0,CHARINDEX('`~`8',lastname)),CHARINDEX('7',SUBSTRING(lastname,0,CHARINDEX('`~`8',lastname)))+2,10) ELSE lastname END) from hrmresource where a.creater=hrmresource.id)
ELSE '' END)
AS "申请人",
(CASE WHEN a.requestlevel = '0' THEN '正常'
WHEN a.requestlevel = '1' THEN '重要'
ELSE '紧急' END) AS "紧急程度",
(CASE WHEN a.currentnodetype = '0' THEN '创建'
WHEN a.currentnodetype = '1' THEN '批准'
WHEN a.currentnodetype = '2' THEN '提交'
ELSE '归档' END) AS "当前状态",
b.LOGID AS "记录ID"
,(CASE WHEN b.logtype = '0' THEN '批准'
WHEN b.logtype = '1' THEN '保存'
WHEN b.logtype = '2' THEN '提交'
WHEN b.logtype = '3' THEN '退回'
WHEN b.logtype = '4' THEN '重新打开'
WHEN b.logtype = '5' THEN '删除'
WHEN b.logtype = '6' THEN '激活'
WHEN b.logtype = '7' THEN '转发'
WHEN b.logtype = '9' THEN '批注'
WHEN b.logtype = 'a' THEN '意见征询'
WHEN b.logtype = 'b' THEN '意见征询回复'
WHEN b.logtype = 'e' THEN '强制归档'
WHEN b.logtype = 'h' THEN '转办'
WHEN b.logtype = 'i' THEN '干预'
WHEN b.logtype = 'j' THEN '转办反馈'
WHEN b.logtype = 's' THEN '督办'
ELSE '抄送' END) AS "签字类型"
,b.operator AS "操作人ID"
,(select (CASE WHEN lastname like '%~`~`7%' THEN SUBSTRING(SUBSTRING(lastname,0,CHARINDEX('`~`8',lastname)),CHARINDEX('7',SUBSTRING(lastname,0,CHARINDEX('`~`8',lastname)))+2,10)
ELSE lastname END) from hrmresource where b.operator=hrmresource.id)AS "操作人"
,(select tb.departmentname from hrmresource ta,HrmDepartment tb where ta.departmentid=tb.id and b.operator=ta.id) AS "操作人部门"
,(select SUBSTRING(SUBSTRING(c.jobtitlename,0,CHARINDEX('`~`8',c.jobtitlename)),CHARINDEX('7',SUBSTRING(c.jobtitlename,0,CHARINDEX('`~`8',c.jobtitlename)))+2,50) from hrmresource a,hrmjobtitles c where a.jobtitle=c.id and b.operator=a.id) as "操作人岗位"
,b.remark AS "签字信息"
,b.receivedPersons AS "接收者名称"
FROM
workflow_requestbase a,
workflow_requestLog b
WHERE
a.requestid = b.requestid

结果：左边是总数据，右边是审批流程明细