SQLserver Delete from where 与Oracle delete from where 的差异

1.SQLserver 版本：

select @@version;

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

 

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

 具体判别重复的方式为：同一卡号、同一消费金额、同一消费窗口、两条消费记录的时间差小于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

3.查询重复记录

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
    );

但是SQLserver不支持这种写法，反而支持连接的方式（oracle不支持inner join 的方式）

delete 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; 

 

转载于:https://www.cnblogs.com/Alex-Zeng/p/5266087.html