解决方案 »
- 新创建的存储过程 用execute immediate 报标识符过长
- 用Odt.net连oracle,一直无法打开连接,若先用pl/sql连接后,就可以用Opt.net连接了
- 存储过程 字符转数字出错,请高手指教
- 为什么“select 1234 into 变量 from dual”报错?
- 在线急等!Oracle触发器!
- Oracle9i触发器的简单问题——涉及new,old
- 这段脚本中的L 和 ;是什么意思?
- 关于NOT IN 语句??
- 一个PL/SQL 函数的意思
- SQL怎么写??
- 应用程序隔一段时间不操作报错 :ora-03114 未连接到oracle
- 关于Linux系统下Oracle 11.2.0.3 打 11.2.0.3.5 PSU error code 73
SELECT ID,ADDS,DATES,
CASE WHEN DATES-PRE<=15/60/24 OR NEXT-DATES<=15/60/24 THEN '满足' ELSE '不满足' END STATUS
FROM(
select ID,ADDS,DATES,
lag(dates,1,null) over(partition by id order by dates) pre,
lead(dates,1,null) over(partition by id order by dates) next
from T)
ORDER BY ID,DATES
SELECT ID,ADDS,DATES,
CASE WHEN DATES-PRE<=15/60/24 OR NEXT-DATES<=15/60/24 THEN '满足' ELSE '不满足' END STATUS
FROM(
select ID,ADDS,DATES,
lag(dates,1,dates-1) over(partition by id order by dates) pre,
lead(dates,1,dates+1) over(partition by id order by dates) next
from T)
ORDER BY ID,DATES
2 (select 324234234 id,'百色市靖西县新靖镇电力小区2号' ADDS,to_date('2014-11-11 1:29','yyyy-mm-dd hh24:mi') dates from dual
3 union all
4 select 324234235 id,'百色市靖西县新靖镇电力小区2号' ADDS,to_date('2014-11-11 1:30','yyyy-mm-dd hh24:mi') dates from dual
5 union all
6 select 324234236 id,'百色市平果县兴平路' ADDS,to_date('2014-11-11 0:41','yyyy-mm-dd hh24:mi') dates from dual
7 union all
8 select 324234237 id,'百色市平果县兴平路' ADDS,to_date('2014-11-11 1:08','yyyy-mm-dd hh24:mi') dates from dual
9 union all
10 select 324234238 id,'百色市田东县田东商都德洲汉堡店' ADDS,to_date('2014-11-11 2:02','yyyy-mm-dd hh24:mi') dates from dual
11 union all
12 select 324234239 id,'百色市田东县田东商都德洲汉堡店' ADDS,to_date('2014-11-11 1:57','yyyy-mm-dd hh24:mi') dates from dual
13 union all
14 select 324234240 id,'百色市田东县田东商都德洲汉堡店' ADDS,to_date('2014-11-11 1:38','yyyy-mm-dd hh24:mi') dates from dual
15 )
16 SELECT ID,ADDS,to_char(DATES,'yyyy-mm-dd hh24:mi') dates,
17 CASE WHEN DATES-PRE<=15/60/24 OR NEXT-DATES<=15/60/24 THEN '满足' ELSE '不满足' END STATUS
18 FROM(
19 select ID,ADDS,DATES,
20 lag(dates,1,dates-1) over(partition by adds order by dates) pre,
21 lead(dates,1,dates+1) over(partition by adds order by dates) next
22 from T)
23 ORDER BY adds,DATES; ID ADDS DATES STATUS
---------- ------------------------------ ---------------- ------
324234234 百色市靖西县新靖镇电力小区2号 2014-11-11 01:29 满足
324234235 百色市靖西县新靖镇电力小区2号 2014-11-11 01:30 满足
324234236 百色市平果县兴平路 2014-11-11 00:41 不满足
324234237 百色市平果县兴平路 2014-11-11 01:08 不满足
324234240 百色市田东县田东商都德洲汉堡店 2014-11-11 01:38 不满足
324234239 百色市田东县田东商都德洲汉堡店 2014-11-11 01:57 满足
324234238 百色市田东县田东商都德洲汉堡店 2014-11-11 02:02 满足已选择7行。SQL>