假设你的表名为T,起始时间S,结束时间E,关键字为ID 以下语句已考虑时间段交叉的情况 WITH T1 AS( select connect_by_root(s) S,E from t T1 connect by prior s<=s and prior e>=s AND PRIOR ID<>ID start with not exists(select 1 from T WHERE S<=T1.s and E>=T1.S AND ID<>T1.ID) ) SELECT SUM(MAX(E)-S) FROM T1 GROUP BY S
T WHERE S<=T1.s and E>=T 这个什么意思啊
not exists(select 1 from T WHERE S<=T1.s and E>=T1.S AND ID<>T1.ID) 这个条件 就是说,当前记录的起始时间不属于任何其他记录代表的时间段
感谢,我想看下明细的记录,我的表名是SHIP_BERTH,不要给我汇总的结果
看明细,把sum去掉就行了 WITH T1 AS( select connect_by_root(s) S,E from t T1 connect by prior s<=s and prior e>=s AND PRIOR ID<>ID start with not exists(select 1 from T WHERE S<=T1.s and E>=T1.S AND ID<>T1.ID) ) SELECT S,MAX(E) AS E FROM T1 GROUP BY S
以下语句已考虑时间段交叉的情况
WITH T1 AS(
select connect_by_root(s) S,E
from t T1
connect by prior s<=s and prior e>=s AND PRIOR ID<>ID
start with not exists(select 1 from T WHERE S<=T1.s and E>=T1.S AND ID<>T1.ID)
)
SELECT SUM(MAX(E)-S)
FROM T1
GROUP BY S
这个条件 就是说,当前记录的起始时间不属于任何其他记录代表的时间段
WITH T1 AS(
select connect_by_root(s) S,E
from t T1
connect by prior s<=s and prior e>=s AND PRIOR ID<>ID
start with not exists(select 1 from T WHERE S<=T1.s and E>=T1.S AND ID<>T1.ID)
)
SELECT S,MAX(E) AS E
FROM T1
GROUP BY S
此外,集算器提供JDBC接口,可以象数据库一样嵌入到应用程序中,用起来很简单。