求1条DELETE语句,第2次发帖(修正了错误).
原始数据
员工 日期 类型
X1 2013-05-06 1
X1 2013-05-07 ---此条数据需要删除
X1 2013-05-08
X2 2013-05-06
X2 2013-05-07 1
X2 2013-05-08 ---此条数据需要删除
-删除后的结果------------------------------
员工 日期 类型
X1 2013-05-06 1
X1 2013-05-08
X2 2013-05-06
X2 2013-05-07 1
---------
需求描述:
当类型为1时,删除第2天的数据.
上1贴,数据有问题,照成大家理解错误,抱歉! 上帖已结,现重新发布!
[解决办法]
处理前先备份
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-04-01 13:59:13
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([员工] varchar(2),[日期] datetime,[类型] int)
insert [huang]
select 'X1','2013-05-06',1 union all
select 'X1','2013-05-07',null union all
select 'X1','2013-05-08',null union all
select 'X2','2013-05-06',null union all
select 'X2','2013-05-07',1 union all
select 'X2','2013-05-08',null
--------------开始查询--------------------------
DELETE a
FROM [huang] a
WHERE EXISTS ( SELECT 1
FROM (SELECT * FROM [huang] WHERE [类型] = 1 )b
WHERE a.[员工] = b.[员工]
AND a.[日期]=DATEADD(dd,1,b.[日期]))
----------------结果----------------------------
/*
*/
