返回列表 發帖

连续日期统计

连续日期统计

表结构:

create table TEST
(
  ID VARCHAR2(8),
  RQ DATE,
  JE NUMBER
)

表数据:


查询语句:


SELECT B.ID, (MAX(B.rq) - MIN(B.rq)+1) DAYS,MIN(B.rq) ks, MAX(B.rq) js,sum(je)
          FROM (SELECT A.*, to_number(to_char(rq, 'yyyyMMdd')) - rownum DAYS
                  FROM (SELECT * FROM Test ORDER BY ID, rq) A) B
         GROUP BY B.ID, B.DAYS  order by B.ID


查询结果:
  1. SELECT B.ID, (MAX(B.rq) - MIN(B.rq)+1) DAYS,MIN(B.rq) ks, MAX(B.rq) js,sum(je)rownum
  2. FROM (SELECT @rownum:=@rownum+1 as rownum, A.*, CAST(CAST(rq as DATE)as SIGNED) - 5
  3.     FROM (SELECT *,@rownum:=0 FROM Test ORDER BY ID, rq) A) B
  4.          GROUP BY B.ID order by B.ID
複製代碼
附件: 您需要登錄才可以下載或查看附件。沒有帳號?註冊

返回列表