直接上表结构
create table P_INV
(
PIV_BILL_NO VARCHAR2(6) not null,
PIV_BR_ID VARCHAR2(8) not null,
PIV_DATE DATE not null,
PIV_YS_AMT NUMBER(12,3) not null,
PIV_HYK_NO VARCHAR2(15),
)
insert into p_inv values('010001','010','2011-10-25',1500,'A150101');
insert into p_inv values('010002','010','2012-10-25',1500,'A150102');
insert into p_inv values('010003','010','2012-10-25',2200,'A150103');
insert into p_inv values('010004','010','2013-10-25',4000,'A150104');
insert into p_inv values('010005','010','2011-1-25',8000,'A150101');
insert into p_inv values('010006','010','2014-10-25',1500,'A150101');
insert into p_inv values('010007','010','2012-10-05',1500,'A150101');
insert into p_inv values('010301','010','2011-10-25',1500,'B150101');
insert into p_inv values('010001','010','2013-10-25',1500,'B150101');
此表P_inv是销售表,字段定义如下:PIV_BILL_NO 小票号,PIV_BR_ID店号,PIV_DATE 消费日期, PIV_YS_AMT 消费金额,PIV_HYK_NO 会员卡号
create table MEM_CARD_EXCHANGE
(
MCE_MEM_FROM VARCHAR2(8) not null,
MCE_MEM_TO VARCHAR2(8) not null,
MCE_MEM_VALID VARCHAR2(8) not null,
MCE_PROCESS_TIME DATE)
insert into mem_card_exchange values(''A150101','A150103','B150101','2013-1-5');
insert into mem_card_exchange values(''A150103','B150101','B150101','2014-1-5')
此表为转卡表,卡升级以及遗失补卡存在此表,字段定义如下:MCE_MEM_FROM 原卡号,MCE_MEM_TO 新卡号,MCE_MEM_VALID 现在有效卡,MCE_PROCESS_TIME 换卡时间;
现在的需求是 统计MCE_MEM_VALID 即 现在有效卡,每年消费满3000的,满3000不满5000的,超过5000的会员卡数有多少?
这里要用到循环嵌套,因为有好些卡换卡好多次,最终使用的是这个卡号MCE_MEM_VALID,然而销售表记录的还是以前的卡消费信息,那我现在还要将之前的消费 算在现在有效卡的消费金额上....
有点绕,各位不清楚的,可以直接回复,求各位高手指点