oracle 分析函数完成号码区段的统计(连续的,不连续的加一条记录)
来源:互联网 发布:海天盛筵 知乎 编辑:程序博客网 时间:2024/06/11 20:58
建表语句:
CREATE TABLE "DY"."TB_MEMCARDINFO" ("MEMCARDINFO_ID" NUMBER NOT NULL ,"VIPCLASS_ID" NUMBER NULL ,"VIPCODE" VARCHAR2(20 BYTE) NULL ,"CARDSTATUS" VARCHAR2(2 BYTE) NULL ,"CREATE_USER" VARCHAR2(12 BYTE) NULL ,"USER_GROUP" VARCHAR2(12 BYTE) NULL ,"CREATE_DATE" DATE NULL ,"MODIFIER" VARCHAR2(12 BYTE) NULL ,"MODI_DATE" DATE NULL ,"FLAG" NUMBER(1) NULL ,"JOINTYPE" VARCHAR2(2 BYTE) NULL ,"INMALL_ID" NUMBER NULL )
这是一张会员卡信息表
下面插入部分数据:
INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593478', '37', '166666981', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593479', '37', '166666982', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593480', '37', '166666983', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593481', '37', '166666984', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593482', '37', '166666985', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593483', '37', '166666986', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593484', '37', '166666987', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593485', '37', '166666988', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593486', '37', '166666989', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593487', '37', '166666990', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593488', '37', '166666991', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593489', '37', '166666992', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593490', '37', '166666993', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593491', '37', '166666994', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593492', '37', '166666995', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593493', '37', '166666996', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593494', '37', '166666997', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593495', '37', '166666998', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1593496', '37', '166666999', '0', 'jiangf03', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'jiangf03', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '24');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592916', '38', '200000419', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592917', '38', '200000420', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592918', '38', '200000421', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592919', '38', '200000422', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592920', '38', '200000423', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592921', '38', '200000424', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592922', '38', '200000425', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592923', '38', '200000426', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592924', '38', '200000427', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592925', '38', '200000428', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592926', '38', '200000429', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592927', '38', '200000430', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592928', '38', '200000431', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592929', '38', '200000432', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592930', '38', '200000433', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592931', '38', '200000434', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592932', '38', '200000435', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592933', '38', '200000436', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592934', '38', '200000437', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592935', '38', '200000438', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592936', '38', '200000439', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592937', '38', '200000440', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592938', '38', '200000441', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592939', '38', '200000442', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');INSERT INTO "DY"."TB_MEMCARDINFO" VALUES ('1592940', '38', '200000443', '0', 'dxd004', '01', TO_DATE('2014-12-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'dxd004', TO_DATE('2014-12-23 15:12:40', 'YYYY-MM-DD HH24:MI:SS'), '0', '0', '238');
下面给出查询要求(这里不加条件),根据操作人,商场,卡类型,入库时间 ,卡状态分组 结果:
下面写出部分查询语句:
select min(vipcode),max(vipcode) ,count(1),CARDSTATUS,INMALL_IDfrom ( select c.vipcode,C.CARDSTATUS ,C.INMALL_ID,row_number() over(partition by c.CARDSTATUS order by c.vipcode)rn from tb_memcardinfo c ORDER BY C.CARDSTATUS,C.INMALL_ID )group by CARDSTATUS,vipcode-rn,CARDSTATUS,INMALL_IDhaving count(*)>0order by cardstatus,min(vipcode);
这里面只根据卡状态来进行分组了,更多的条件读者可以自己限定或留言。
查询结果:
可以看出,号段已经分开显示。
这里主要使用了
<span style="color:#ff0000;">row_number() over(partition by c.CARDSTATUS order by c.vipcode)</span>这个函数是oracle中分析函数,在此不做具体说明。
0 0
- oracle 分析函数完成号码区段的统计(连续的,不连续的加一条记录)
- oracle 如何实现上一条、下一条、查找不连续的值(转)
- SQL获得连续的记录的统计
- ORACLE SQL: 查询连续号码段并合并的方法
- 另类获得连续号码的求法
- oracle中某列连续相同值的记录数统计(一个简单的例子)
- 在号码池取连续号码的算法
- 统计连续字段的个数
- mysql统计某列值连续出现次数小于五次的记录
- 按时间段统计,不连续的日期要补上
- sql删除一条记录后其他记录的id自动迁移,使id连续
- 查询不连续的数据
- identity不连续的问题
- 编码 统计连续字符串中连续字母的个数。
- 统计字符串中连续1和连续0的个数
- oracle 得到连续不重复的数字序列
- Oracle求值的连续范围
- Oracle 查询连续的年份
- C#获取本地打印机列表,并将指定打印机设置为默认打印机
- 反渗透设备:RO反渗透设备应用范围详细解析
- 17周项目5--玩日期时间
- Python提取xml中的字段
- ACM函数模板开源
- oracle 分析函数完成号码区段的统计(连续的,不连续的加一条记录)
- 从事Android开发我们要注意哪些?
- 如何写出高效率的正则表达式
- 异常机制及throw与throws的区别
- AndroidStudio中常用的快捷键
- 编程一些问题摘要
- 改造 Cydia Substrate 框架用于函数内代码的HOOK
- 系统配置工具(网络与打印机)与硬件侦测(鸟哥的Linux私房菜)
- SSH框架