各位高手,目前我要实现这么一个功能:如果我通过SQL查询出表里的数据是:
开始时间 结束时间 时间差
20:00:00 21:00:00 60
等等
(开始时间不一定是20:00:00,可能是任意大于18:30:00)若果开始时间大于18:30:00,我想在这条数据之前添加一条数据是 开始时间 结束时间 时间差 如何用SQL查询实现?
18:30:00 20:00:00 90
开始时间 结束时间 时间差
20:00:00 21:00:00 60
等等
(开始时间不一定是20:00:00,可能是任意大于18:30:00)若果开始时间大于18:30:00,我想在这条数据之前添加一条数据是 开始时间 结束时间 时间差 如何用SQL查询实现?
18:30:00 20:00:00 90
SELECT '18:30:00' AS ST,'21:20:00' AS ET,'10200' AS SC FROM DUAL
UNION ALL
SELECT '20:00:00' AS ST,'21:30:00' AS ET,'5400' AS SC FROM DUAL
UNION ALL
SELECT '20:30:00' AS ST,'21:40:00' AS ET,'4200' AS SC FROM DUAL
UNION ALL
SELECT '20:10:20' AS ST,'21:50:30' AS ET,'6010' AS SC FROM DUAL
)
SELECT ST,FET,NVL(SC,(AHH24-BHH24)*3600+(AMI-BMI)*60+(ASS-BSS)) AS SC FROM (
SELECT RN,
ST,
ET,
NST,
NVL(ET, NST) AS FET,
SC,
TO_CHAR(TO_DATE(NVL(ET, NST), 'HH24:MI:SS'), 'HH24') AS AHH24,
TO_CHAR(TO_DATE(NVL(ET, NST), 'HH24:MI:SS'), 'MI') AS AMI,
TO_CHAR(TO_DATE(NVL(ET, NST), 'HH24:MI:SS'), 'SS') AS ASS,
TO_CHAR(TO_DATE(ST, 'HH24:MI:SS'), 'HH24') AS BHH24,
TO_CHAR(TO_DATE(ST, 'HH24:MI:SS'), 'MI') AS BMI,
TO_CHAR(TO_DATE(ST, 'HH24:MI:SS'), 'SS') AS BSS
FROM (SELECT RN,
ST,
ET,
SC,
LEAD(ST) OVER(PARTITION BY RN ORDER BY RN) AS NST
FROM (select ROWNUM AS RN, ST, ET, SC
FROM TEST
UNION ALL
SELECT ROWNUM AS RN, '18:30:00' AS ST, '' AS ET, '' AS SC
FROM DUAL
CONNECT BY ROWNUM <= (SELECT COUNT(1) FROM TEST))
ORDER BY RN, ET DESC)
WHERE ST <> NVL(ET, NST))
===================================================
1 18:30:00 21:20:00 10200
2 18:30:00 20:00:00 5400
3 20:00:00 21:30:00 5400
4 18:30:00 20:30:00 7200
5 20:30:00 21:40:00 4200
6 18:30:00 20:10:20 6020
7 20:10:20 21:50:30 6010
时差为秒级