高级sql

1.orcle    

delete from where

需求场景，生产系统中的数据为刷卡记录，存在重复的情况，现在需要删除重复的数据。

 具体判别重复的方式为：同一卡号、同一消费金额、同一消费窗口、两条消费记录的时间差小于30秒则认为是重复的。样例数据如下：

2012210856 9.00 2016-03-02 11:47:05.000 消费 后勤集团\饮食中心\桂香园餐厅新\二楼\黑椒鸡柳饭 本专科生 7686
2012210856 9.00 2016-03-02 11:47:30.000 消费 后勤集团\饮食中心\桂香园餐厅新\二楼\黑椒鸡柳饭 本专科生 7687
2012210856 9.00 2016-03-02 11:47:48.000 消费 后勤集团\饮食中心\桂香园餐厅新\二楼\黑椒鸡柳饭 本专科生 7688

查询重复记录

select a.* from dbo.ODS_CCNU_zengx_distinct a inner join dbo.ODS_CCNU_zengx_distinct b
on a.smt_salaryno = b.smt_salaryno  --同一卡号
    and a.smt_transmoney=b.smt_transmoney --同一消费金额
    and a.smt_org_name = b.smt_org_name  --同一消费窗口
    and datediff(ss,a.smt_dealdatetime,b.smt_dealdatetime)>=0 
    and datediff(ss,a.smt_dealdatetime,b.smt_dealdatetime)<30 --时间间隔为30秒之内
    and a.rownum_distinct != b.rownum_distinct
order by  a.smt_salaryno,a.smt_dealdatetime  ; 

或者这样

select a.* from [dbo].ODS_CCNU_zengx_distinct a
where exists( select 1 from [dbo].ODS_CCNU_zengx_distinct b
    where a.smt_salaryno = b.smt_salaryno  --同一卡号
    and a.smt_transmoney=b.smt_transmoney --同一消费金额
    and a.smt_org_name = b.smt_org_name  --同一消费窗口
    and datediff(ss,a.smt_dealdatetime,b.smt_dealdatetime)>=0 
    and datediff(ss,a.smt_dealdatetime,b.smt_dealdatetime)<30 --时间间隔为30秒之内
    and a.rownum_distinct != b.rownum_distinct
    )
order by a.smt_salaryno,a.smt_dealdatetime ;

删除重复记录，如果在oracle中可以这样写

delete from dbo.ODS_CCNU_zengx_distinct a

where exists( select 1 from dbo.ODS_CCNU_zengx_distinct b
    where a.smt_salaryno = b.smt_salaryno  --同一卡号
    and a.smt_transmoney=b.smt_transmoney --同一消费金额
    and a.smt_org_name = b.smt_org_name  --同一消费窗口
    and datediff(ss,a.smt_dealdatetime,b.smt_dealdatetime)>0 
    and datediff(ss,a.smt_dealdatetime,b.smt_dealdatetime)<30 --时间间隔为30秒之内
    and a.rownum_distinct != b.rownum_distinct
    );

貌似datediff写法oracle没有

2. oracle  

rank() over(partition)的使用

 有的时候会遇到这样的问题，我们需要查询一张表，而且要按照业务排序，比如我需要如下的结果：

    地区   日期    费用  产品编号   用户编号

     290 201202 258 1              s1
     290 201202 200 1              s5
     290 201202 100 1              s100
     290 201202 90   2              s7
     290 201202 88   2              s9
     290 201202 10   2              s12。 

     领导让我出一张报表，需要看到每一个业务的收费前三名是那些客户。这个时候用rank() over(partition)是一个很不错的选择。

     我的测试表就像上面例子中的表一样，不过数据稍微多一点点。给大家一个截图：

     

      可以看到我每一个项目都有5条记录，我只取前三，那么SQL如下：

      

SELECT A.AREA_ID, A.ACCT_MONTH, A.FEE, A.ITEM_ID, A.USER_ID
  FROM (SELECT T.AREA_ID,
               T.ACCT_MONTH,
               T.FEE,
               T.ITEM_ID,
               T.USER_ID,
               RANK() OVER(PARTITION BY T.ITEM_ID ORDER BY T.FEE DESC) RK
          FROM TEST T) A
 WHERE RK < 4;

参考：https://www.cnblogs.com/Alex-Zeng/p/5266087.html

           http://www.cnblogs.com/wingsless/archive/2012/02/04/2338292.html