主页 > 知识库 > 数据库 > SQL server >

SQL技巧之查询连续的记录

来源:中国IT实验室 作者:佚名 发表于:2013-01-05 14:36  点击:
需求说明: 需要查询出某个客户某一年那些天是有连续办理过业务 实现SQL如下: 创建表: create table test_num (tyear number, tdate date); 测试数据: insert into test_num select 2014,trunc(sysdate)-1 from dual union all select 2014,trunc(s
需求说明:  需要查询出某个客户某一年那些天是有连续办理过业务
  实现SQL如下:
  创建表:
  create table test_num
  (tyear number,
  tdate date);
  测试数据:
  insert into test_num
  select 2014,trunc(sysdate)-1 from dual union all
  select 2014,trunc(sysdate)-002 from dual union all
  select 2014,trunc(sysdate)-003 from dual union all
  select 2014,trunc(sysdate)-004 from dual union all
  select 2014,trunc(sysdate)-005 from dual union all
  select 2014,trunc(sysdate)-007 from dual union all
  select 2014,trunc(sysdate)-008 from dual union all
  select 2014,trunc(sysdate)-009 from dual union all
  select 2013,trunc(sysdate)-120 from dual union all
  select 2013,trunc(sysdate)-121 from dual union all
  select 2013,trunc(sysdate)-122 from dual union all
  select 2013,trunc(sysdate)-124 from dual union all
  select 2013,trunc(sysdate)-125 from dual union all
  select 2013,trunc(sysdate)-127 from dual union all
  select 2015,trunc(sysdate)-099 from dual union all
  select 2015,trunc(sysdate)-100 from dual union all
  select 2015,trunc(sysdate)-101 from dual union all
  select 2015,trunc(sysdate)-102 from dual union all
  select 2015,trunc(sysdate)-104 from dual union all
  select 2015,trunc(sysdate)-105 from dual;
  写SQL:
  SELECT TYEAR, MIN(TDATE) AS STARTDATE, MAX(TDATE), COUNT(TYEAR) AS ENDNUM
  FROM (SELECT A.*, A.TDATE - ROWNUM AS GNUM
  FROM (SELECT * FROM TEST_NUM ORDER BY TYEAR, TDATE) A)
  GROUP BY TYEAR, GNUM
  ORDER BY TYEAR, MIN(TDATE)

    有帮助
    (0)
    0%
    没帮助
    (0)
    0%