Question

Oracle数据库 sql高手请进来：取出某段时间段内，时间中天数连续的数据

表如下：vister
id name visit_time
1 张三 2001-10-01 08:15:20
2 张三 2001-10-02 08:15:20
3 张三 2001-10-03 08:15:20
4 张三 2001-10-07 08:15:20
5 张三 2001-10-08 08:15:20
6 张三 2001-10-11 08:15:20
7 张三 2001-10-12 08:15:20
8 张三 2001-10-13 08:15:20
9 张三 2001-10-14 08:15:20

我要的结果是 在2001-09-15到2001-11-01中张三连续访问最长天数的一组数据，结果如下
id name visit_time
6 张三 2001-10-11 08:15:20
7 张三 2001-10-12 08:15:20
8 张三 2001-10-13 08:15:20
9 张三 2001-10-14 08:15:20

我用的是Oracle数据库
求高手解答，附代码！！！谢谢！！！

查询后结果
2013-8-8 14:10:47
2013-8-8 14:20:47
2013-8-8 14:30:47
2013-8-8 14:40:47
2013-8-8 15:30:47
2013-8-8 15:40:47
2013-8-8 15:50:47
2013-8-8 16:00:47
2013-8-8 16:10:47

sql 查询出来结果，这样就可以了
date1 date2
2013-8-8 14:10:47 2013-8-8 14:40:47
2013-8-8 15:30:47 2013-8-8 16:10:47
把时间连续的分成一组，是Oracle数据库 应该要用row_number() 函数

剩下的我会在后台解决！！

Answer 1

直接用查询就可以得到你要的结果：

select ID,NAME,VISIT_TIME
   from
      (
      select ID,NAME,VISIT_TIME
            ,CNT
            ,max(CNT) over () MX
         from
            (
            select ID,NAME,VISIT_TIME
                  ,count(MINDATE) over (partition by MINDATE) CNT
               from
                  (
                  select ID,NAME,VISIT_TIME
                        ,MINDATE
                     from 
                        (
                        select ID,NAME,VISIT_TIME
                              ,row_number() over (order by VISIT_TIME) RN
                           from VISTER  t
                           where t.NAME = '张三'
                        ) t
                     model
                     dimension by (t.RN)
                     measures(ID,NAME,VISIT_TIME,trunc(VISIT_TIME) VISIT_DATE,trunc(t.VISIT_TIME) as MINDATE)
                     rules upsert all
                     (
                     MINDATE[any] = 
                        case 
                           when VISIT_DATE[currentv()] - VISIT_DATE[currentv()-1] <= 1 then 
                              MINDATE[currentv()-1] 
                           else 
                              VISIT_DATE[currentv()]
                        end
                     )
                     order by VISIT_TIME
                  )
            )
      )
   where CNT = MX

追问

你是Oracle数据库的sql 还是 sqlServer的sql啊？？我用的是Oracle会报错啊！无效数字！！！楼主

追答

1、这是Oracle的SQL

2、我提供的Model查询基于模拟的数据表结构如下：

create table VISTER
(
  id         NUMBER,
  name       VARCHAR2(20),
  visit_time DATE
)

会不会是字段的类型弄错了？

3、模拟的数据如下：

ID NAME VISIT_TIME

1 张三 2001-10-1 8:15:20

2 张三 2001-10-2 8:15:20

3 张三 2001-10-3 8:15:20

4 张三 2001-10-7 8:15:20

5 张三 2001-10-8 8:15:20

6 张三 2001-10-11 8:15:20

7 张三 2001-10-12 8:15:20

8 张三 2001-10-13 8:15:20

9 张三 2001-10-14 8:15:20

10 张三 2001-10-14 8:15:25

11 张三 2001-10-2 8:15:26

4、这个SQL执行的结果如下：

ID NAME VISIT_TIME

6 张三 2001-10-11 8:15:20

7 张三 2001-10-12 8:15:20

8 张三 2001-10-13 8:15:20

9 张三 2001-10-14 8:15:20

10 张三 2001-10-14 8:15:25

追问

2013-8-8 14:30:47
2013-8-8 14:40:47
2013-8-8 15:30:47
2013-8-8 15:40:47
2013-8-8 15:50:47
2013-8-8 16:00:47
是分钟！不是月份
我用这个转换过了to_date(visit_time,'yyyy-mm-dd HH24:MI:SS')会报错！
请问这是怎么回事？我是新手，sql不是很好，想向你请教一下，怎么改？

追答

修改完后的SQL如下：

                        (
                        select ID,NAME,to_date(VISIT_TIME,'YYYY-MM-DD HH24:MI:SS') VISIT_TIME
                              ,row_number() over (order by to_date(VISIT_TIME,'YYYY-MM-DD HH24:MI:SS')) RN
                           from VISTER  t
                           where t.NAME = '张三'
                              and to_date(VISIT_TIME,'YYYY-MM-DD HH24:MI:SS') >= to_date('2001-09-15','YYYY-MM-DD')
                              and to_date(VISIT_TIME,'YYYY-MM-DD HH24:MI:SS') <= to_date('2001-11-01','YYYY-MM-DD')
                        ) t

追问

时间字段 varchar2(20)
数据
2013-8-8 14:30:47
2013-8-8 14:40:47
2013-8-8 15:30:47
2013-8-8 15:40:47
2013-8-8 15:50:47
结果
2013-8-8 15:30:47
2013-8-8 15:40:47
2013-8-8 15:50:47
没出来？最长分钟，麻烦您看下！

追答

你的提问中是：
“我要的结果是 在2001-09-15到2001-11-01中张三连续访问最长天数的一组数据”

怎么变成了“2013-8-8”？
SQL中有过滤条件的，修改一下就好了。

追问

不好意思！我没修改成功！年月日是相同的，我要求分钟连续最长时间段！帮忙改下，我的sql不好！！！在下万分感谢！！！随便问下，你的qq多少我想加你，如果方便的话，技术交流。谢谢
1

追答

“分钟连续”，多长时间内算是连续的？

Answer 2

哪里要这么复杂....鉴于你按每10分钟来连续排列有点奇怪..我写了一个按照日期来连续的sql..

select b.name, min(b.visit_time), max(b.visit_time)
from (
  select a.*, (a.visit_time - rownum) cc
  from (
    select * from vister order by name, visit_time
  ) a
) b
group by b.name, b.cc;

追问

报错！！ select a.*, (a.visit_time - rownum) cc
from (
select * from vister order by name, visit_time
) a

追答

我这边执行没问题..你是什么环境?
另外,报的什么错?

追问

2013-8-8 14:30:47
2013-8-8 14:40:47
2013-8-8 15:30:47
2013-8-8 15:40:47
2013-8-8 15:50:47
2013-8-8 16:00:47

是分钟！不是月份

我用这个转换过了to_date(visit_time,'yyyy-mm-dd HH24:MI:SS')会报错！
请问这是怎么回事？我是新手，sql不是很好，想向你请教一下，

追答

你的visit_time是varchar2吗?并且格式是"2001-10-14 08:15:20"这样的吗?或许是你格式的问题..你可以执行以下select to_date('2001-10-14 08:15:20', 'yyyy-mm-dd hh24:mi:ss') from dual;这个试试..要是执行这个都不成..那估计是你执行sql的工具的问题...

分钟的话,你的数据一定是每隔10分钟记录一个数据吗?或者这个问,,是以10分钟为一个单位吗?

我先把sql写了吧...为了跟你保持数据一致,sql如下:

select b.name, min(b.visit_time), max(b.visit_time)
from (
  select a.*, (to_date(a.visit_time, 'yyyy-mm-dd hh24:mi:ss') - rownum*10/(60*24)) cc
  from (
    select * from vister order by name, visit_time
  ) a
) b
group by b.name, b.cc;

运行结果如下:

追问

我也显示出来了，首先我非常感谢你对我的帮助。可是目前有个小问题？rownum*11/(60*24)是10分钟，这里能写活吗，不写死，应为有时候是10分钟，11分钟，12分钟间隔这种情况都有。有办法解决吗？

追答

如果时间间隔是一样的..比如,都是10或者11,12..这样可以写死没问题..可以如果数据中混合这10,11,12..这样就没办法了...而且上面的例子中,有个必须的条件是所有时间的秒数是一样的..如果不一样,,就必须截取掉再做判断..

追问

谢谢你对我的帮助！顺便问下，你qq是多少啊，想加你一下，技术交流，如果方便的话，谢谢！

Answer 3

我给你个思路吧！相必你基本的orcacl操作都会，因为我没有装这相数据，所以也没有办法给你写代码!

第一步:先有count(日期),name,日期(格式为年月日) group by 日期,name (对count进行高到低排列)
第二步:取出最高日期,
第三步:显示最高日期下的详细信息;

Answer 4

参照一下这个 http://zhidao.baidu.com/question/1860129509886885707.html?oldq=1

Answer 5

建议使用存储过程吧。

追问

?怎么写？代码！谢谢！！！