要求用SQL 语句表达出来
必须是同一个的相同信息,不是同一天的可以不删
我觉得这个问题可以从插入数据是解决,插入式用触发器,或者建唯一约束.
delete from table where postdate between convert(varchar(10),getdate(),120) and convert(varchar(10),dateadd(day,1,getdate()),120)
这个最少应该知道 哪个字段是必须不能相同的。才好删除
假如字段d表示信息时间,A表示信息内容,
sql语句:delete from xx x1 where (select count(*) from xx x2 where x2.A=x1.A and day(x2.d)=day(x1.d))>0
IF OBJECT_ID('[xinxilin]') IS NOT NULL
DROP TABLE [xinxilin]
GO
CREATE TABLE [xinxilin] ([日期] [datetime],[单号] [nvarchar](10),[借出书本] [nvarchar](10),[书本总金额] [numeric](4,2))
INSERT INTO [xinxilin]
SELECT '2010-06-15 09:00','201001','书本1','10.00' UNION ALL
SELECT '2010-06-15 11:00','201002','书本2','20.00' UNION ALL
SELECT '2010-06-17 12:01','201003','书本3','10.00' UNION ALL
SELECT '2010-06-18 08:00','201004','书本4','40.00' UNION ALL
SELECT '2010-06-18 12:00','201005','书本1','10.00' UNION ALL
SELECT '2010-07-15 07:00','201006','书本2','20.00' UNION ALL
SELECT '2010-07-15 09:00','201007','书本3','10.00' UNION ALL
SELECT '2010-07-15 15:00','201008','书本4','40.00' UNION ALL
SELECT '2010-07-15 12:34','201009','书本5','10.00' UNION ALL
SELECT '2010-07-15 18:00','201010','书本6','10.00' UNION ALL
SELECT '2010-07-16 14:00','201011','书本7','10.00' UNION ALL
SELECT '2010-07-16 19:00','201012','书本8','10.00'
select * from [xinxilin] order by [日期] asc
go
/*
日期 单号 借出书本 书本总金额
2010-06-15 09:00:00.000 201001 书本1 10.00
2010-06-15 11:00:00.000 201002 书本2 20.00
2010-06-17 12:01:00.000 201003 书本3 10.00
2010-06-18 08:00:00.000 201004 书本4 40.00
2010-06-18 12:00:00.000 201005 书本1 10.00
2010-07-15 07:00:00.000 201006 书本2 20.00
2010-07-15 09:00:00.000 201007 书本3 10.00
2010-07-15 12:34:00.000 201009 书本5 10.00
2010-07-15 15:00:00.000 201008 书本4 40.00
2010-07-15 18:00:00.000 201010 书本6 10.00
2010-07-16 14:00:00.000 201011 书本7 10.00
2010-07-16 19:00:00.000 201012 书本8 10.00
(12 row(s) affected)
*/
delete from [xinxilin]
where [单号] not in
(
select min( [单号]) from [xinxilin] group by convert(nvarchar(11),日期,120)
)
go
/*(7 row(s) affected)*/
select * from [xinxilin] order by [日期] asc
go
/*
日期 单号 借出书本 书本总金额
2010-06-15 09:00:00.000 201001 书本1 10.00
2010-06-17 12:01:00.000 201003 书本3 10.00
2010-06-18 08:00:00.000 201004 书本4 40.00
2010-07-15 07:00:00.000 201006 书本2 20.00
2010-07-16 14:00:00.000 201011 书本7 10.00
*/
同一个相同信息?是某个字段内容相同还是记录相同。
delete temp2 from (
select * ,ROW_NUMBER() over(PARTITION by Title,money,area_oneid,area_twoid,area_threeid,class_oneid,class_twoid,class_threeid,contact,telephone,qq,msn,email,address,templateid,picurl,usual1,usual2,usual3,usual4,usual5,
checked,state,inform,code,zt,ipaddress order by id) as rn
from temp1
) temp2 where rn>1