我导出数据做透视表。WITH handle_data AS (
SELECT
ocbi.biz_info_oid,
ocfh.flow_handle_oid,
ocfh.user_code,
NVL(u.user_name, ocfh.user_code) AS operator_name,
NVL(org.unit_name, '未知处室') AS dept_name,
ocs.operation_date,
CASE
WHEN ocbi.item_code = '01' AND ocbi.sub_item = '01' THEN '普通来文'
WHEN ocbi.item_code = '01' AND ocbi.sub_item = '02' THEN '会议件'
WHEN ocbi.item_code = '02' THEN '发文'
WHEN ocbi.item_code = '03' AND ocbi.sub_item = '03' THEN '内部文'
END AS file_type,
ocs.operation_code
FROM oa_common_flow_handle ocfh
INNER JOIN oa_common_flow_step_ins ocfs
ON ocfh.step_ins_oid = ocfs.step_ins_oid
INNER JOIN oa_common_biz_info ocbi
ON ocbi.flow_ins_oid = ocfs.flow_ins_oid
INNER JOIN oa_common_step_operation ocs
ON ocfh.flow_handle_oid = ocs.flow_handle_oid
LEFT JOIN users u
ON ocfh.user_code = u.reference_oid
LEFT JOIN oa_unit_org org
ON ocfh.unit_oid = org.unit_oid
WHERE ocs.operation_code IN ('04','08')
AND TO_CHAR(ocs.operation_date,'YYYY') = '2025'
AND (
(ocbi.item_code = '01' AND ocbi.sub_item IN ('01','02'))
OR ocbi.item_code = '02'
OR (ocbi.item_code = '03' AND ocbi.sub_item = '03')
)
),
offtime_handle AS (
SELECT *
FROM (
SELECT
hd.biz_info_oid,
hd.flow_handle_oid,
hd.user_code,
hd.operator_name,
hd.dept_name,
hd.file_type,
hd.operation_date,
TO_CHAR(hd.operation_date,'MM') AS month_id,
ROW_NUMBER() OVER (
PARTITION BY hd.flow_handle_oid, hd.user_code
ORDER BY hd.operation_date
) AS rn
FROM handle_data hd
WHERE
WEEKDAY(hd.operation_date) IN (0,6)
OR (
WEEKDAY(hd.operation_date) BETWEEN 1 AND 5
AND (TO_NUMBER(TO_CHAR(hd.operation_date,'HH24MI')) < 900
OR TO_NUMBER(TO_CHAR(hd.operation_date,'HH24MI')) >= 1800)
)
) WHERE rn = 1
)
SELECT
ofh.dept_name AS 处室,
ofh.operator_name AS 处理人,
ofh.file_type AS 文类,
SUM(CASE WHEN month_id='01' THEN 1 ELSE 0 END) AS 一月,
SUM(CASE WHEN month_id='02' THEN 1 ELSE 0 END) AS 二月,
SUM(CASE WHEN month_id='03' THEN 1 ELSE 0 END) AS 三月,
SUM(CASE WHEN month_id='04' THEN 1 ELSE 0 END) AS 四月,
SUM(CASE WHEN month_id='05' THEN 1 ELSE 0 END) AS 五月,
SUM(CASE WHEN month_id='06' THEN 1 ELSE 0 END) AS 六月,
SUM(CASE WHEN month_id='07' THEN 1 ELSE 0 END) AS 七月,
SUM(CASE WHEN month_id='08' THEN 1 ELSE 0 END) AS 八月,
SUM(CASE WHEN month_id='09' THEN 1 ELSE 0 END) AS 九月,
SUM(CASE WHEN month_id='10' THEN 1 ELSE 0 END) AS 十月,
SUM(CASE WHEN month_id='11' THEN 1 ELSE 0 END) AS 十一月,
SUM(CASE WHEN month_id='12' THEN 1 ELSE 0 END) AS 十二月,
COUNT(DISTINCT ofh.flow_handle_oid || '-' || ofh.user_code) AS 年度合计
FROM offtime_handle ofh
GROUP BY
ofh.dept_name,
ofh.operator_name,
ofh.file_type
ORDER BY
ofh.dept_name,
ofh.operator_name,
ofh.file_type;我要分别查询2025年每个处室、每个人办件情况，按在oa_common_step_operation中 04=办理，03=签收，06=签批，10=办结，09=退回，05=更新，08=完成；
我的是达梦数据库DMv8，公文类型=CASE WHEN ocbi.item_code = '01'
AND ocbi.sub_item = '01' THEN '普通来文' WHEN ocbi.item_code = '01'
AND ocbi.sub_item = '02' THEN '会议件' WHEN ocbi.item_code = '02' THEN '发文' WHEN ocbi.item_code = '03'
AND ocbi.sub_item = '03' THEN '内部文' END AS file_type；
Oracle SQL 加号符号解释
本文解释了Oracle SQL中使用加号符号(+)在连接条件中的含义，即表示左连接。详细介绍了当加号放在连接条件右侧时如何返回所有左侧记录及与之匹配的右侧记录。
扫一扫
813
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
