解决方案 »
- java存储过程?
- 请教南大通用数据库问题
- oracle 备份问题
- 请问索引组织表+ 分区表的方式,如何创建、建立索引,帮助优化。谢谢
- 存储过程的参数为数组该怎么传递进去?Oracle+Delphi高手请进,
- 一次访问插入多条记录的问题
- 索引中丢失in或out参数?
- 请问查询时怎么用“0”替代空值?
- 从效率上考虑,ROWNUM = 1应该放在WHERE条件中的最前面还是最后面?
- 老板催得急啊,各位好心人,帮帮忙吧如何在jsp中把word文档上传到oracle数据库
- 应用程序隔一段时间不操作报错 :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>