oracle sql语句想计算出A员工所使用程序的总天数

Question

oraclesql

Answer 1

;WITH start AS(
SELECT start_time,ROW_NUMBER()OVER(ORDER BY start_time)RN
FROM TB T1
WHERE NOT EXISTS(
SELECT 1 FROM TB T2 WHERE T1.A1<>T2.A1
AND T1.start_time>T2.start_time AND T1.start_time<=T2.stop_time
)
GROUP BY start_time
)
,stop AS(
SELECT stop_time,ROW_NUMBER()OVER(ORDER BY stop_time)RN
FROM TB T1
WHERE NOT EXISTS(
SELECT 1 FROM TB T2 WHERE T1.A1<>T2.A1
AND T1.stop_time>=T2.start_time AND T1.stop_time<T2.stop_time
)
GROUP BY stop_time
)
SELECT SUM(DATEDIFF(DAY,start_time,stop_time))SUMDAY
FROM start JOIN stop ON start.RN = stop.RN
--这边是MSSQL的语法
--你在这边转化成你ORACLE的语法

Answer 2

这样的语法出来的结果是    RESIDENCE_NO TS
1 000337713002           7
而如果我直接用最大值最小值去计算出来的数据是
    RESIDENCE_NO TS
1 000337713002        11
我实际手工去算的话，也是11天。原始数据如下
                                                start_time                    stop_time
1 000337713002         2014-12-24 10:57            2015-1-3 10:06
 1 000337713002         2014-12-23 16:05           2014-12-24 10:33

Answer 3

首先，你示例数据只精确到天，你现在又精确到分，有些地方肯定要调整下其次，怎么算都不会是7天，你把你改后的ORACLE代码贴上来我看下，大概能看懂的还有，有个情况，要说清楚情况一:
                                                start_time                    stop_time
1 000337713002         2014-12-24 10:57            2015-1-3 10:06
 1 000337713002         2014-12-23 16:05           2014-12-24 10:33
这个是11天那这个
                                                start_time                    stop_time
1 000337713002         2014-12-25 10:57            2015-1-3 10:06
 1 000337713002         2014-12-23 16:05           2014-12-24 10:33
这个也是11天吗

Answer 4

那这个
                                                start_time                    stop_time
1 000337713002         2014-12-25 10:57            2015-1-3 10:06
 1 000337713002         2014-12-23 16:05           2014-12-24 10:33
这个也是11天吗
对这个也是11天。
select
distinct  start.residence_no residence_no,
sum(trunc(stop.stop_time)-trunc(start.start_time)) DAY
from
(
    SELECT     distinct  residence_no,start_time,ROW_NUMBER()OVER(ORDER BY start_time)RN
    FROM TB T1
    WHERE NOT EXISTS(
        SELECT 1 FROM TB T2 WHERE T1.A1<>T2.A1
            AND T1.start_time>T2.start_time AND T1.start_time<=T2.stop_time
        )
    GROUP BY start_time,residence_no
) start 
,(
    SELECT distinct  residence_no,stop_time,ROW_NUMBER()OVER(ORDER BY stop_time)RN
    FROM TB T1
    WHERE NOT EXISTS(
        SELECT 1 FROM TB T2 WHERE T1.A1<>T2.A1
            AND T1.stop_time>=T2.start_time AND T1.stop_time<T2.stop_time
        )
    GROUP BY stop_time,residence_no
)stop where  start.RN = stop.RN and start.residence_no=stop.residence_no
group by start.residence_no
----这其中的residence_no为' 000337713002 '

Answer 5

select start.residence_no residence_no,
SUM(DATEDIFF(DAY,start_time,stop_time))DAY
--sum(trunc(stop.stop_time)-trunc(start.start_time)) DAY
from
(
    SELECT residence_no,start_time,ROW_NUMBER()OVER(PARTITION BY residence_no ORDER BY start_time)RN
    FROM TB T1
    WHERE NOT EXISTS(
        SELECT 1 FROM TB T2 WHERE T1.residence_no=T2.residence_no AND T1.A1<>T2.A1
            AND CONVERT(VARCHAR(8),T1.start_time,112)>CONVERT(VARCHAR(8),T2.start_time,112)
            AND CONVERT(VARCHAR(8),T1.start_time,112)<=CONVERT(VARCHAR(8),T2.stop_time,112)+1
            --ORACLE应该是这样的
            --AND TO_CHAR(T1.start_time,'YYYYMMDD')>TO_CHAR(T2.start_time,'YYYYMMDD')
            --AND TO_CHAR(T1.start_time,'YYYYMMDD')<=TO_CHAR(T2.stop_time+1,'YYYYMMDD')
        )
    GROUP BY start_time,residence_no
) start 
,(
    SELECT residence_no,stop_time,ROW_NUMBER()OVER(PARTITION BY residence_no ORDER BY stop_time)RN
    FROM TB T1
    WHERE NOT EXISTS(
        SELECT 1 FROM TB T2 WHERE T1.residence_no=T2.residence_no AND T1.A1<>T2.A1
            AND CONVERT(VARCHAR(8),T1.stop_time,112)>=CONVERT(VARCHAR(8),T2.start_time,112)-1
            AND CONVERT(VARCHAR(8),T1.stop_time,112)<CONVERT(VARCHAR(8),T2.stop_time,112)
            --ORACLE应该是这样的
            --AND TO_CHAR(T1.stop_time,'YYYYMMDD')>=TO_CHAR(T2.start_time-1,'YYYYMMDD')
            --AND TO_CHAR(T1.stop_time,'YYYYMMDD')<TO_CHAR(T2.stop_time,'YYYYMMDD')
        )
    GROUP BY stop_time,residence_no
)stop
where start.RN=stop.RN and start.residence_no=stop.residence_no
group by start.residence_no你试下，相应的地方自己注释和反注释下