本文共 4974 字，大约阅读时间需要 16 分钟。

paip.输入法编程---带ord gudin去重复-

作者Attilax ，  EMAIL:1466519819@qq.com 来源：attilax的专栏 地址：

--------查询重复(不同ORD)

SELECT

 hezi,  atian,  gudin,    count(id) AS num FROM  gaopinzi WHERE  LENGTH(atian) > 0 and   ( del is null    or del=0)  and lang='chinese' GROUP BY  hezi,  atian   HAVING  num > 1

-----------加入临时表.

DELETE from tmp_tsiku;

  insert tmp_tsiku(hezi,atian,gudin,lang)

SELECT

 hezi,  atian,  gudin,    count(id) AS num FROM  gaopinzi WHERE  LENGTH(atian) > 0 and   ( del is null    or del=0)  and lang='chinese' GROUP BY  hezi,  atian   HAVING  num > 1 ; select *   from tmp_tsiku

去重复存储过程 conf_del ---------------------- 原理如下： for( tmp_tsiku ) if(getTsityao_count(hezi,py)) { baolyeu_id=  get_top1(hezi,py); del_other(hezi,py,baolyeu_id); 

}

 

BEGIN

 #Routine body goes here... declare tmpName varchar(200) default '' ; declare var_ati varchar(200) default '' ;   declare havgudin int;   declare gundi_id int;   declare rownum int;   declare tsityao_count int;   declare baolyeu_id int;

declare tmpint int;

DECLARE isRecordNotFound int;    declare cur1 CURSOR FOR   select hezi,atian from tmp_tsiku where 1=1   ; declare continue handler for not found set isRecordNotFound = 1; -- Oracle的PL/SQL的指针有个隐性变量%notfound，        -- Mysql是通过一个Error handler的声明来进行判断的，        -- declare continue handler for Not found (do some action);        -- 在Mysql里当游标遍历溢出时，会出现一个预定义的NOT FOUND的Error，        -- 我们处理这个Error并定义一个continue的handler就可以了     -- 下面一句不能没有，否则将会进不了while循环        set isRecordNotFound = 0; set rownum=1;   /*开游标*/ #set tmpName=; set  var_ati      OPEN cur1;

         /*游标向下走一步*/

         FETCH cur1 INTO tmpName,var_ati;

         /* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */

      WHILE ( isRecordNotFound = 0 ) DO

              set tsityao_count=getTsityao_count(tmpName,var_ati);

       if tsityao_count>1 THEN                      select havgudin,rownum ;                        set

baolyeu_id=    get_top1(tmpName,var_ati);

             

select  gundi_id,rownum ;

             set

tmpint=  del_other(tmpName,var_ati,baolyeu_id);         

 

                               end if;

       set rownum=rownum+1;

/*yao jya jeig select ,beri zweiheu yg result b show chwlai.. */ select 'the end';       /*游标向下走一步*/

       FETCH cur1 INTO tmpName,var_ati;

      END WHILE;

      CLOSE cur1;

 

END

 

-------getTsityao_count---------

BEGIN  #Routine body goes here... DECLARE gudinid int ;

set @gudinid=  (

SELECT

  COUNT(*) FROM  gaopinzi WHERE     (del IS NULL OR del = 0) AND lang = 'chinese' AND hezi = hezi AND atian =py

 );

 RETURN @gudinid; END --------get_top1----------- BEGIN  #Routine body goes here... DECLARE gudinid int ;

set @gudinid=  ( select  id from gaopinzi   where lang='chinese'  and HEZI=hezix and ATIAN=py

  and (del is null or del=0)

  order by gudin desc,ord,id limit 1

);  RETURN @gudinid; END

---------del_other---------

BEGIN  #Routine body goes here... #select SQL_NO_CACHE  del_no_gudin ('一','y',3192) c1

declare tmpName INT;

  /* update  gaopinzi set del=1,deltime=now(),dely='del no-gudin'  */  #SET NAMES 'utf8'; insert tmp(id,hezi,py)    select id,hezi,atian from gaopinzi 

where lang='chinese'  and HEZI=hezix and atian=py

and  ( del is null    or del=0)    and id!=baolyeuid  

;

 RETURN  tmpName;

 

END

检查得到的tmp是否OK.

-----------

触发器日志

--------- 以便进行误删除恢复.. CREATE TRIGGER `deladdtime` AFTER UPDATE ON `gaopinzi` FOR EACH ROW begin   insert  logx(idop,eventx,timex,demo,hezi,pyold,pynew)values( old.id,'update rec',now(),'',old.hezi,old.atian,new.atian); end;

删除虫复 ----------- update  gaopinzi set del=1,deltime=now(),dely='del no-gudin'   where id in (select id from tmp)

 

----------恢复误删除的记录

select * from logx WHERE id>=6 and id<=10

 (select idop from logx WHERE id>=6 and id<=10); select * from gaopinzi  where id in (13083,15319,15736,16030,137815); UPDATE gaopinzi set del=0,deltime=now(),dely='hweif' where id in (13083,15319,15736,16030,137815); select * from gaopinzi where id=137815

 

------------------已下为测试SQL-------------- ------------------已下为测试SQL--------------

select * from gaopinzi   where lang='chinese'  and HEZI='七' and ( del is null    or del=0) order by id

limit 7;

select  * from gaopinzi   where lang='chinese'  and HEZI='一'  and ( del is null    or del=0) exec QUERY_chonf_nosame_ord

-----查询是否有重复的记录...

SELECT

  COUNT(*) FROM  gaopinzi WHERE  LENGTH(hezi) = 3 AND (del IS NULL OR del = 0) AND lang = 'chinese' AND hezi = '针' AND atian = 'jenjs' ORDER BY  gudin DESC,  ord

-------得到要保留的ID

SELECT  * FROM  gaopinzi WHERE  LENGTH(hezi) = 3 AND (del IS NULL OR del = 0) AND lang = 'chinese' AND hezi = '针' AND atian = 'jenjs' ORDER BY  gudin DESC,  ord LIMIT 1

------删除其他记录.

update  gaopinzi set del=1  where

LENGTH(hezi) = 3 AND (del IS NULL OR del = 0) AND lang = 'chinese' AND hezi = '针' AND atian = 'jenjs' and

 

select * from gaopinzi 

 

where lang='chinese'  and HEZI='一' and atian='y'

and  ( del is null    or del=0)   and gudin=0 and id!=3192

=============

update gaopinzi  set del=1 ,dely='test' where id=7106 and atian='cy'

update gaopinzi  set del=1 ,dely='test' where id=7106 and atian='cy' select * from   gaopinzi    where id=7106

select * from  tmp

select hezi,atian from   gaopinzi  where lang='chinese'   and (del is null or del=0)  and LENGTH(hezi)=3 

and ord=99   order by hezi

select * from gaopinzi   where lang='chinese'   and (del is null or del=0)  and LENGTH(hezi)=3  and ord=99 

order by hezi

select havgudin('针','jen')

select havgudin('一','y')

转载地址：http://dramf.baihongyu.com/