oracle查询连续记录，如何查

Question

如：表 r_data
     mr_id          amt_ym   cons_no   this_read_pq
   70023506062      201304   000034927   0
   70023506063      201303   000034927   0
   70023506064      201304   000034937   0
   70023506065      201302   000034928   0
   70023506066      201304   000033827   0
   70023506067      201302   000034927   0mr_id 是主键，求怎样查询出 连续N个月 this_read_pq都为0的cons_no
结果就像
70023506062      201304   000034927   0
70023506063      201303   000034927   0
70023506067      201302   000034927   0
就是说，假如我选定的月份为1月到4月，那么某个cons_no的this_read_pq每个月都为0才满足Oracle

Answer 1

对..mr_id    和cons_no   没有要求么?
2月有两个mr_id为啥是7 而不是5

Answer 2

with t as(
select 70023506062 as mr_id,201304 as amt_ym, 000034927 as cons_no, 0 as this_read_pq from dual union all
select 70023506063, 201303, 000034927, 0 from dual union all
select 70023506064, 201304, 000034937, 0 from dual union all
select 70023506065, 201302, 000034928, 0 from dual union all
select 70023506066, 201304, 000033827, 0 from dual union all
select 70023506067, 201302, 000034927, 0 from dual
)
SELECT MIN(T.MR_ID),T.AMT_YM,MIN(T.CONS_NO)
  FROM T
 GROUP BY T.AMT_YM
 HAVING  SUM(DECODE(THIS_READ_PQ, 0, 0, 1))=0;

Answer 3

没有要求，mr_id 前几位是固定的，后几位是根据序列来的，cons_no其实也一样。是另一个表里的主键。

Answer 4

ChenZw> WITH T AS(
  2  SELECT 70023506062 AS MR_ID,201304 AS AMT_YM, 000034927 AS CONS_NO, 0 AS THIS_READ_PQ FROM DUAL UNION ALL
  3  SELECT 70023506063, 201303, 000034927, 0 FROM DUAL UNION ALL
  4  SELECT 70023506064, 201304, 000034937, 0 FROM DUAL UNION ALL
  5  SELECT 70023506065, 201302, 000034928, 0 FROM DUAL UNION ALL
  6  SELECT 70023506066, 201304, 000033827, 0 FROM DUAL UNION ALL
  7  SELECT 70023506067, 201302, 000034927, 0 FROM DUAL UNION ALL
  8  SELECT 70023506068, 201305, 000034938, 1 FROM DUAL
  9  )
 10  SELECT MIN(T.MR_ID),T.AMT_YM,MIN(T.CONS_NO)
 11    FROM T
 12    WHERE T.AMT_YM BETWEEN 201302 AND 201305
 13   GROUP BY T.AMT_YM
 14   HAVING  SUM(DECODE(THIS_READ_PQ, 0, 0, 1))=0;MIN(T.MR_ID)     AMT_YM MIN(T.CONS_NO)
------------ ---------- --------------
  7.0024E+10     201304          33827
  7.0024E+10     201302          34927
  7.0024E+10     201303          34927已选择3行。

Answer 5

就一查询
 WITH T AS(
  2  SELECT 70023506062 AS MR_ID,201304 AS AMT_YM, 000034927 AS CONS_NO, 0 AS THIS_READ_PQ FROM DUAL UNION ALL
  3  SELECT 70023506063, 201303, 000034927, 0 FROM DUAL UNION ALL
  4  SELECT 70023506064, 201304, 000034937, 0 FROM DUAL UNION ALL
  5  SELECT 70023506065, 201302, 000034928, 0 FROM DUAL UNION ALL
  6  SELECT 70023506066, 201304, 000033827, 0 FROM DUAL UNION ALL
  7  SELECT 70023506067, 201302, 000034927, 0 FROM DUAL UNION ALL
  8  SELECT 70023506068, 201305, 000034938, 1 FROM DUAL
  9  )
主要是这一堆。咋搞，如果是几十万条的话

Answer 6

...用上面的WITH语句是模拟数据的..因为我没有你的表结构和表的内容,所以我自己模拟了个数据出来.你可以直接在你的环境下执行下面的内容 SELECT MIN(T.MR_ID),T.AMT_YM,MIN(T.CONS_NO)
   FROM 你的表名  T
    WHERE T.AMT_YM BETWEEN 201302 AND 201305
   GROUP BY T.AMT_YM
   HAVING  SUM(DECODE(THIS_READ_PQ, 0, 0, 1))=0;

Answer 7

嗯，这个我测试了下，还是有问题的。
70023506062      201304   000034927   0
70023506063      201303   000034926   0
70023506067      201302   000034927   0
假如表中有上面3条记录。
按你写的sql。这3条都查出来了。
可是要求是连续3个月。。而且cons_no是一样的啊。。实际上，上面3条记录没有满足条件的。虽然amt_ym 连续起来了。但cons_no 不一样的。

Answer 8

还必须是连续的同一cons_no.70023506062      201304   000034927   0
70023506063      201303   000034927   0
70023506067      201302   000034927   0
70023506068      201301   000034927   0这种效果才对的
不是包含，。。用>= 或者 between ..and..这样查出来的。是只要在这区间里满足条件的都就查出来了。。
如：某一个cons_no 在1-5月，必须每个月都为0 。
这种的才符合要求

Answer 9

cons_no 必须是相同 而且 amt_ym 必须是连续的么？
70023506062      201304   000034927   0
70023506063      201303   000034927   0
70023506067      201302   000034927   0上述应该是符合条件的吧？

Answer 10

我用你的sql，查了下
70023506062      201304   000034927   0
70023506063      201303   000034827   0
70023506067      201302   000034927   0
像这种的也就出来了。。
年月是连续的。。可3月份 的cons_no 和2月。4月不一样。。

Answer 11

TRY:WITH T AS(
SELECT 70023506062 AS MR_ID,201302 AS AMT_YM, 000034926 AS CONS_NO, 0 AS THIS_READ_PQ FROM DUAL UNION ALL
SELECT 70023506063, 201303, 000034926, 0 FROM DUAL UNION ALL
SELECT 70023506064, 201303, 000034927, 0 FROM DUAL UNION ALL
SELECT 70023506065, 201302, 000034928, 0 FROM DUAL UNION ALL
SELECT 70023506066, 201304, 000034927, 0 FROM DUAL UNION ALL
SELECT 70023506067, 201302, 000034927, 0 FROM DUAL
)
SELECT * 
FROM T A 
WHERE EXISTS(
  SELECT 1 FROM T B 
  WHERE ((A.AMT_YM)+1 = B.AMT_YM AND A.CONS_NO = B.CONS_NO)
  OR ((A.AMT_YM)-1 = B.AMT_YM AND A.CONS_NO = B.CONS_NO)
  ) 
  ORDER BY A.CONS_NO,A.AMT_YM;

Answer 12

附上;表结构》/*==============================================================*/
/* Table: R_DATA                                                */
/*==============================================================*/
create table R_DATA  (
   MR_ID                NUMBER(16)                      not null,
   MR_PLAN_NO           VARCHAR2(16)                    not null,
   APP_NO               VARCHAR2(16),
   CALC_ID              NUMBER(16),
   AMT_YM               VARCHAR2(6),
   CONS_NO              VARCHAR2(16),
   ORG_NO               VARCHAR2(16),
   METER_ID             NUMBER(16)                      not null,
   MP_ID                NUMBER(16)                      not null,
   MR_SN                NUMBER(5),
   AVG_PQ               NUMBER(16),
   MR_DIGIT             NUMBER(10,6),
   READ_TYPE_CODE       VARCHAR2(8)                     not null,
   T_FACTOR             NUMBER(10,2)                    not null,
   LAST_MR_NUM          NUMBER(12,4)                    not null,
   LAST_MR_PQ           NUMBER(16),
   THIS_READ            NUMBER(12,4),
   THIS_READ_PQ         NUMBER(16),
   MR_STATUS_CODE       VARCHAR2(8)                     not null,
   EXCP_TYPE_CODE       VARCHAR2(8),
   LAST_MR_DATE         DATE,
   THIS_YMD             DATE,
   SRC_CODE             VARCHAR2(8)                     not null,
   RELA_APP_NO          VARCHAR2(16),
   MR_FACTOR            VARCHAR2(8),
   ACTUAL_MODE          VARCHAR2(8),
   METER_DATE           DATE,
   constraint PK_R_DATA primary key (MR_ID)
);
comment on column R_DATA.MR_ID is
'本实体记录的唯一标识，产生规则为流水号，作为应用时标识关联关系。';comment on column R_DATA.MR_PLAN_NO is
'本实体记录外部唯一标识，产生规则为流水号';comment on column R_DATA.APP_NO is
'流程实例的唯一标识，可自行编码';comment on column R_DATA.CALC_ID is
'对应所算费结果所对应的电费记录';comment on column R_DATA.AMT_YM is
'电费年月，此处用于描述抄表年月';comment on column R_DATA.CONS_NO is
'用户的外部标识comment on column R_DATA.ORG_NO is
'一般是用户的直接供电管理单位，;comment on column R_DATA.METER_ID is
'电能表号。';comment on column R_DATA.MP_ID is
'';comment on column R_DATA.MR_SN is
'';comment on column R_DATA.AVG_PQ is
'平均电量';comment on column R_DATA.MR_DIGIT is
'示数位数，采用n.d形式。n为整数部分位数，d为小数部分位数。';comment on column R_DATA.READ_TYPE_CODE is
'示数类型，并包括：
需量(尖峰)
需量(峰)
需量(平)
需量(谷)';comment on column R_DATA.T_FACTOR is
'综合倍率。';comment on column R_DATA.LAST_MR_NUM is
'上次抄见示数：用于存放用户本次抄见示数。';comment on column R_DATA.LAST_MR_PQ is
'上次抄见电量：用于存放用户上次 计费抄见电量。单位：千瓦时';comment on column R_DATA.THIS_READ is
'本次抄见示数。';comment on column R_DATA.THIS_READ_PQ is
'本次抄见电量。有功表、无功表抄见电量计算：抄见电量i=（本次示数i－上次示数i）× 综合倍率；
需量表抄见电量计算：抄见最大需量i= 本次示数i × 综合倍率
其中i表示各时段。
单位：千瓦时
';comment on column R_DATA.MR_STATUS_CODE is
'抄表状态，已抄、未抄。';comment on column R_DATA.EXCP_TYPE_CODE is
'异常抄表类别：计量异常、门闭、违约用电、窃电、翻转、倒转 、倒转且翻转。';comment on column R_DATA.LAST_MR_DATE is
'上次抄表日期：用于存放用户上次计费抄表日期。';comment on column R_DATA.THIS_YMD is
'本次抄表日期。';comment on column R_DATA.SRC_CODE is
'抄表数据来源';comment on column R_DATA.RELA_APP_NO is
'业扩变更等旧申请编号。';comment on column R_DATA.MR_FACTOR is
'定义电能表的抄表难度系数值，根据量测域中事先定义的抄表难度标准系数和具体数据计算而来。';comment on column R_DATA.ACTUAL_MODE is
'实际使用抄表方式';comment on column R_DATA.METER_DATE is
'电能表对时前日期';

Answer 13

需求：查询连续N个月（不确定，用户选择的，前台传参）this_read_pq 为0的cons_no
由于同一个cons_no，在同一月份是有N条记录的。这由 READ_TYPE_CODE 来确定，
所以所查的就成为sum(this_read_pq)=0

Answer 14

是啊。。连续月份。。
就如2-5 也就是 2.3.4.5。这4个月，某一客户同时为0的，所以需要分组了。哎
必须满足三个条件： 一：连续月份 如：2-5月
                   二：同一客户，所以要分组的哦。
                   三：sum(this_read_pq) = 0

Answer 15

TRY:WITH T AS(
SELECT 70023506062 AS MR_ID,201302 AS AMT_YM, 000034926 AS CONS_NO, 0 AS THIS_READ_PQ FROM DUAL UNION ALL
SELECT 70023506063, 201303, 000034926, 0 FROM DUAL UNION ALL
SELECT 70023506064, 201303, 000034927, 0 FROM DUAL UNION ALL
SELECT 70023506065, 201302, 000034928, 0 FROM DUAL UNION ALL
SELECT 70023506066, 201304, 000034927, 0 FROM DUAL UNION ALL
SELECT 70023506067, 201302, 000034927, 0 FROM DUAL
)
SELECT WMSYS.WM_CONCAT(MR_ID) AS MR_ID,WMSYS.WM_CONCAT(AMT_YM) AS AMT_YM,CONS_NO,COUNT(1) AS MONS
FROM T A 
WHERE A.AMT_YM BETWEEN 201302 AND 201303
  AND  EXISTS(
  SELECT 1 FROM T B 
  WHERE (TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(A.AMT_YM,'YYYYMM'),1),'YYYYMM')) = B.AMT_YM AND A.CONS_NO = B.CONS_NO)
  OR (TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(A.AMT_YM,'YYYYMM'),-1),'YYYYMM')) = B.AMT_YM AND A.CONS_NO = B.CONS_NO)
  ) 
  GROUP BY A.CONS_NO;

Answer 16

上基于函数的两个索引,计算两个日期,这样子,就可以不用每一列都去进行计算了.CREATE INDEX IND_FUN_T ON T (当前日期的下个月)
CREATE INDEX IND_FUN_T ON T (当前日期的上个月)

Answer 17

mr_id  作为分组条件，然后用lag()函数进行分析，就可以得到连续的记录了

Answer 18

还是有问题。
sum(this_read_pq)=0 加上后，查询出的结果。。
我再瞧瞧。

Answer 19

 SELECT mr_id, amt_ym, cons_no, this_read_pq
  FROM (SELECT COUNT(1) OVER(PARTITION BY cons_no, RN) AS RN,
               mr_id,
               amt_ym,
               cons_no,
               this_read_pq
          FROM (SELECT amt_ym - ROW_NUMBER() OVER(PARTITION BY cons_no ORDER BY amt_ym) AS RN,
                       TEST.*
                  FROM TEST
                 WHERE this_read_pq = '0'
                   AND amt_ym >= '2013' || '04'
                   AND amt_ym <= '2013' || '07'))
 WHERE RN = 7-4+1
比如你传进来的月份是4-7月
'04'，'07'用lpad

Answer 20

WITH T AS(
SELECT 70023506062 AS MR_ID,201302 AS AMT_YM, 000034926 AS CONS_NO, 0 AS THIS_READ_PQ FROM DUAL UNION ALL
SELECT 70023506063, 201303, 000034926, 0 FROM DUAL UNION ALL
SELECT 70023506064, 201303, 000034927, 0 FROM DUAL UNION ALL
SELECT 70023506065, 201302, 000034928, 0 FROM DUAL UNION ALL
SELECT 70023506066, 201304, 000034927, 0 FROM DUAL UNION ALL
SELECT 70023506067, 201304, 000034927, 0 FROM DUAL UNION ALL
SELECT 70023506068, 201302, 000034927, 0 FROM DUAL
)
SELECT WMSYS.WM_CONCAT(MR_ID) AS MR_ID,WMSYS.WM_CONCAT(AMT_YM),CONS_NO,COUNT(1) AS MONS
FROM T A 
WHERE A.AMT_YM BETWEEN 201302 AND 201306
  AND  EXISTS(
  SELECT 1 FROM T B 
  WHERE (TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(A.AMT_YM,'YYYYMM'),1),'YYYYMM')) = B.AMT_YM AND A.CONS_NO = B.CONS_NO)
  OR (TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(A.AMT_YM,'YYYYMM'),-1),'YYYYMM')) = B.AMT_YM AND A.CONS_NO = B.CONS_NO)
  ) 
GROUP BY A.CONS_NO
HAVING SUM(DECODE(A.THIS_READ_PQ, 0, 0, 1))=0;MR_ID WMSYS.WM_CONCAT(AMT_YM) CONS_NO MONS
70023506062,70023506063    201302,201303    34926 2
70023506064,70023506068,70023506067,70023506066     201303,201302,201304,201304     34927 4
 201302,201303
 201303,201302,201304,201304这个是体现连续哈..... 我觉着你还是要去看写这个SQL的思路,而不是结果.

Answer 21

谢啦、、、这sql还是。。咳咳，
思路我了解了。
嗯、、、
其实。。可能我说的有点乱吧、、
我还是搞了个比较笨的方法。select cons_no,count(distinct amt_ym) as yms,sum(this_read_pq) from r_data 
where amt_ym between '201302' and '201304'
group by cons_no having sum(this_read_pq)=0 and count(distinct amt_ym)>=3 /*这个3 是不固定的 由 前台传的，也就是 between '201302' and '201304' 之间的差值 */