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'电能表对时前日期';
WITH T AS( SELECT 70023506062 AS MR_ID,201302 AS AMT_YM, 000034926 AS CONS_NO, 0 AS THIS_READ_PQ FROM DUAL UNION ALLSELECT 70023506063, 201303, 000034926, 0 FROM DUAL UNION ALLSELECT 70023506064, 201303, 000034927, 0 FROM DUAL UNION ALLSELECT 70023506065, 201302, 000034928, 0 FROM DUAL UNION ALLSELECT 70023506066, 201304, 000034927, 0 FROM DUAL UNION ALLSELECT 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;
上一页 [1] [2] [3]
