|
SQL实现:
SELECT COUNT(*) FROM (
SELECT h,m,s
,LAG(h) OVER(ORDER BY n) last_h
,LAG(m) OVER(ORDER BY n) last_m
,LAG(s) OVER(ORDER BY n) last_s
FROM (SELECT MOD(360*10.5/12+(LEVEL-1)*360/(12*60*60),360) AS h
,MOD(360*0.5+(LEVEL-1)*360/(60*60),360) AS m
,MOD((LEVEL-1)*360/60,360) s
,LEVEL n
FROM DUAL
CONNECT BY LEVEL<=12*60*60+1
)
)
WHERE last_s<last_m AND DECODE(s,0,360,s)>=DECODE(m,0,360,m)
OR
last_s<last_h AND DECODE(s,0,360,s)>=DECODE(h,0,360,h)
OR
last_m<last_h AND DECODE(m,0,360,m)>=DECODE(h,0,360,h);
COUNT(*)
----------
1426
SQL只能分割到秒,上述计算的原理是:如果上一秒,秒针落后于分针,这一秒变成领先,就认为发生了一次交叠。 |
|