重复行算作一条记录的方法
现在有如下的表，想把t1表中的每个月id重复的行，算作一条，并把其中重复的行的一行change列标记为Y，其他重复的行都标记为N。如果没有重复行的，就标记为Y。我用的如下方法实现的。
现在看一下原表的状态，如下：
SQL> select * from t1 order by foi_ordertime;
ID CHANGE FOI_ORDERT
---------- ---------- ----------
1 2015-01-02
1 2015-01-03
1 2015-01-04
3 2015-01-09
4 2015-01-10
5 2015-01-11
1 2015-02-04
1 2015-02-05
1 2015-02-06
1 2015-02-07
8 2015-02-11
ID CHANGE FOI_ORDERT
---------- ---------- ----------
9 2015-02-12
9 2015-02-12
2 2015-03-07
2 2015-03-08
2 2015-03-09
16 rows selected.
然后我把t1表的change列全备标记为N：
SQL> update t1 set change='N';
16 rows updated.
然后我用如下匿名块来判断，如果有重复行的记录，在其中重复行中选择其中一行标记为Y，其余标记为N。如果没有重复行的，就标记为Y。
SQL> begin
2 for i in 01..12
3 loop
4 update t1 a set change='Y'
5 where to_char(foi_ordertime,'mm')=i and a.rowid in (
6 select min(b.rowid) from t1 b where b.id=a.id and to_char(foi_ordertime,'mm')=i
7 );
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select * from t1 order by foi_ordertime;
ID CHANGE FOI_ORDERT
---------- ---------- ----------
1 Y 2015-01-02
1 N 2015-01-03
1 N 2015-01-04
3 Y 2015-01-09
4 Y 2015-01-10
5 Y 2015-01-11
1 Y 2015-02-04
1 N 2015-02-05
1 N 2015-02-06
1 N 2015-02-07
8 Y 2015-02-11
ID CHANGE FOI_ORDERT
---------- ---------- ----------
9 Y 2015-02-12
9 N 2015-02-12
2 Y 2015-03-07
2 N 2015-03-08
2 N 2015-03-09
再附一个相关的存储过程的写法：
create or replace procedure panyh(v_cusid in varchar2, v_time in varchar2) as
v_con integer;
begin
delete from F_ORDERINFO_DETAIL_PAN t
where substr(t.order_date, 0, 4) = v_time
and customer_id = v_cusid;
commit;
insert into F_ORDERINFO_DETAIL_PAN
select t.*, 'Y'
from f_orderinfo_detail t
where substr(t.order_date, 0, 4) = v_time
AND T.CUSTOMER_ID = v_cusid;
commit;
update F_ORDERINFO_DETAIL_PAN set is_charge_flag='N' where order_type not in ('JBCX');
update F_ORDERINFO_DETAIL_PAN set is_charge_flag='N' where order_status not in ('查到');
for c1 in (select * from F_ORDERINFO_DETAIL_PAN t where substr(t.order_date,0,4)=v_time and t.order_type='JBCX' and t.order_status='查到' order by foi_orderno ) loop
select count(*)
into v_con
from F_ORDERINFO_DETAIL_PAN t
where t.order_date = c1.order_date
AND T.CUSTOMER_ID = v_cusid
and t.order_type = 'JBCX'
and t.order_status = '查到'
and t.foi_orderno=c1.foi_orderno
and t.foi_key in
(select foi_key
from F_ORDERINFO_DETAIL_PAN t
where to_date(foi_ordertime,'yyyy-mm-dd hh24:mi:ss') >=to_date(c1.foi_ordertime,'yyyy-mm-dd hh24:mi:ss')-30
and foi_ordertime <c1.foi_ordertime
and foi_orderno not in (c1.foi_orderno)
and t.is_charge_flag='Y'
and t.order_type='JBCX'
and t.order_status='查到');
if v_con >0
then
update F_ORDERINFO_DETAIL_PAN t set t.is_charge_flag='N' where t.foi_orderno=c1.foi_orderno;
commit;
end if;
end loop;
end;
#######################
下面这个是我弟写的
create or replace procedure panyh5(v_cusid in varchar2, v_time in varchar2) as
v_count NUMBER;
begin
delete from F_ORDERINFO_DETAIL_PAN t
where substr(t.order_date, 0, 4) = v_time
and customer_id = v_cusid;
commit;
insert into F_ORDERINFO_DETAIL_PAN
select t.*, 'N'
from f_orderinfo_detail t
where substr(t.order_date, 0, 4) = v_time
AND T.CUSTOMER_ID = v_cusid;
commit;
update F_ORDERINFO_DETAIL_PAN a set is_charge_flag='Y'
where substr(order_date,0,4)=v_time and a.rowid in (
select min(b.rowid) from F_ORDERINFO_DETAIL_PAN b where b.foi_key=a.foi_key and substr(order_date,0,4)=v_time );
v_count := v_count + 1;
IF v_count >= 1000 THEN
commit;
end if;
commit;
end;
#######################
下面这个是我弟写的
create or replace procedure panyh5(v_cusid in varchar2, v_time in varchar2) as
v_count NUMBER;
begin
delete from F_ORDERINFO_DETAIL_PAN t
where substr(t.order_date, 0, 4) = v_time
and customer_id = v_cusid;
commit;
insert into F_ORDERINFO_DETAIL_PAN
select t.*, 'N'
from f_orderinfo_detail t
where substr(t.order_date, 0, 4) = v_time
AND T.CUSTOMER_ID = v_cusid;
commit;
update F_ORDERINFO_DETAIL_PAN a set is_charge_flag='Y'
where substr(order_date,0,4)=v_time and a.rowid in (
select min(b.rowid) from F_ORDERINFO_DETAIL_PAN b where b.foi_key=a.foi_key and substr(order_date,0,4)=v_time );
v_count := v_count + 1;
IF v_count >= 1000 THEN
commit;
end if;
commit;
end;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28916011/viewspace-1756359/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
用zabbix监控mysql的主从复制
下一篇：
mysql优化--博森瑞
请登录后发表评论
登录
全部评论
