解决魅族应用中心淘宝广告SQL

感谢newkid

WITH TAOBAO_WORDS AS ---淘宝体广告关键字集合表（可以任意添加）
 (SELECT '亲' AS WORD
    FROM DUAL
  UNION ALL
  SELECT '赚' AS WORD
    FROM DUAL
  UNION ALL
  SELECT '钱' AS WORD FROM DUAL)

SELECT *
  from (SELECT SUM(REGEXP_COUNT('亲，还在淘宝购物，赚钱', WORD)) 限度--这里的'亲，还在淘宝购物，赚钱'在实际中为后台数据库的评论字段
          FROM dual
          JOIN TAOBAO_WORDS
            ON INSTR('亲，还在淘宝购物，赚钱', WORD) > 0)--同上 其中WORD为上面淘宝体广告关键字集合
 WHERE 限度 >= 2 --外层嵌套了一个SELECT，可以对淘宝体广告文字出现次数进行控制

 2013-04-15 23:08:22 更新

1，首先建了淘宝体广告表

select * from taobao_ad

2，魅族应用中心评论模拟表

select * from mz_soft

3，sql如下，这里需要用group by 才能得到每行评论的广告字数量（newkid的话：你的里层应该带GROUP BY M.ID
假设你的M表有个主键叫做ID。）

select *
  from (SELECT m.users,
               m.comments,
               sum(REGEXP_COUNT(m.comments, t.words)) 限度
          FROM mz_soft m
          JOIN taobao_ad t
            ON INSTR(m.comments, t.words) > 0
         group by m.users, m.comments)
 where 限度 >=4

 

这里正常评论1和2没有出现，1没有出现是因为它的INSTR(m.comments, t.words) > 0就不满足，没有一个广告语，2是因为它的淘宝体数量才有3个（电，脑，电脑），不满足‘限度 >=4’。

4，下面是触发器，可以一劳永逸，不要每次查询来手工删除了，可以做到事前控制，广告评论发布的时候就不让保存

create or replace trigger ad
  before insert on mz_soft  
  for each row
declare
  -- local variables here
  counts integer;
begin
  select sum(REGEXP_COUNT(:new.comments, t.words)) into counts
          FROM mz_soft m
          JOIN taobao_ad t
            ON INSTR(:new.comments, t.words) > 0;
   if counts>=4 then
    raise_application_error(-20001,'用户：' ||:new.users||'你的评论含有大量广告用户，不能发布
    ，再次发布将会封ID！');
  
      end if;    
end ad;

 

可以看到这个评论‘诚聘:网上兼职、适合全职妈妈，上班族，大学生。有电脑’有9个淘宝体，可以明显的确定为广告（是否为广告可以通过外层的where条件限制）

至于报错之后，把用户id还可以传入到另外的表作为广告人的证据，下次再发就封杀，做到自动，这里就不写了

 

 

 结算中心20万信息化扣款

select *
  from (select b.interestdate,
               a.accidcode,
               a.accidname,
               b.summary,
               trunc(b.debitamount,2)debit,  --round(b.debitamount,2)debit,
               trunc(b.creditamount,2)credit,-- to_char(debitamount,'999,999,999.00')debit,
               b.pk_account,
               b.pk_voucher,
               count(distinct b.pk_account) over(partition by b.pk_voucher, b.interestdate) rn
          from fts_voucher_b b, bd_accid a
         where b.pk_account = a.pk_accid
           and b.summary not like '%0.4%'  --去除外进证0.4%
           and b.summary not like '%科技创新%' --去除科技创新风险
           and substr(interestdate,1,4)>='2012' --信息化扣款从2012年开始
           and b.pk_account <> '1162N51000000000032B') -- 结算中心 '1162N51000000000032B' 总部 '1162N51000000000032C'
 where pk_voucher in(select pk_voucher from fts_voucher_b where pk_account='1162N51000000000032C')
    and  debit =200000
   and rn >= 2
   order by substr(interestdate,1,4), accidcode -- to_char(to_date(interestdate,'yyyy-mm-dd'),'yyyy')