链接：https://pan.baidu.com/s/1jPfQ8hQhvaC3a8s5OTCOPw 

提取码：nnar 

 

实现的SQL如下： SELECT ID, SUM (C) FROM (SELECT ID, RN, (MAX (END_TIM) ­ MIN (BEG_TIM)) * 24 C FROM (SELECT ID, BEG_TIM, END_TIM, SUM (F) OVER (PARTITION BY ID ORDER BY BEG_TIM) AS RN FROM (SELECT T.* , CASE WHEN MAX (END_TIM) OVER (PARTITION BY ID O RDER BY BEG_TIM ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) >= BEG_TIM THE N 0 ELSE 1 END AS F FROM TAB_0 T)) GROUP BY ID, RN) GROUP BY ID 数据如下： WITH TAB_0 AS (SELECT 1 ROWID_, 1 ID, TO_DATE ('20200103 060000', 'YYYYMMDD HH24MISS') BEG_TIM, TO_DATE ('20200103 080000', 'YYYYMMDD HH24MISS') END_TIM, 'admin ' OPER FROM DUAL UNION ALL SELECT 2 ROWID_, 1 ID, TO_DATE ('20200103 050000', 'YYYYMMDD HH24MISS') BEG_TIM, TO_DATE ('20200103 063000', 'YYYYMMDD HH24MISS') END_TIM, 'admin ' OPER FROM DUAL UNION ALL SELECT 3 ROWID_, 1 ID, TO_DATE ('20200103 100000', 'YYYYMMDD HH24MISS') BEG_TIM, TO_DATE ('20200103 150000', 'YYYYMMDD HH24MISS') END_TIM, 'admin ' OPER FROM DUAL UNION ALL SELECT 4 ROWID_, 1 ID, TO_DATE ('20200103 140000', 'YYYYMMDD HH24MISS') BEG_TIM, TO_DATE ('20200103 160000', 'YYYYMMDD HH24MISS') END_TIM, 'admin ' OPER FROM DUAL UNION ALL SELECT 4 ROWID_, 1 ID, TO_DATE ('20200103 180000', 'YYYYMMDD HH24MISS') BEG_TIM, TO_DATE ('20200103 200000', 'YYYYMMDD HH24MISS') END_TIM, 'admin ' OPER

FROM DUAL UNION ALL SELECT 5 ROWID_, 1 ID, TO_DATE ('20200103 170000', 'YYYYMMDD HH24MISS') BEG_TIM, TO_DATE ('20200103 190000', 'YYYYMMDD HH24MISS') END_TIM, 'admin ' OPER FROM DUAL UNION ALL SELECT 6 ROWID_, 1 ID, TO_DATE ('20200103 070000', 'YYYYMMDD HH24MISS') BEG_TIM, TO_DATE ('20200103 100000', 'YYYYMMDD HH24MISS') END_TIM, 'admin ' OPER FROM DUAL) SELECT * FROM TAB_0 ORDER BY BEG_TIM ASC