如何删除同一天相同的信息
0
悬赏园豆：50
[已关闭问题]
要求用SQL 语句表达出来
必须是同一个的相同信息，不是同一天的可以不删
问题补充：
删除的其它字段都相同，只有时间，同一天可能是一天的上午某时，也可能是同一天下午的某时。并且要留下时间靠前的一条信息。
我写了一个，但只能是删除同一条信息，留一条，不能精确到同一天。有点纠结，哪个帮改下 时间的字段是postdate
另以前的SQL如下
delete from [xinxilin].[dbo].[nts_infodata]
where ID not in (select max(ID) from [xinxilin].[dbo].[nts_infodata] group 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)
其他回答(5)
0
我觉得这个问题可以从插入数据是解决,插入式用触发器,或者建唯一约束.
delete from table where postdate between convert(varchar(10),getdate(),120) and convert(varchar(10),dateadd(day,1,getdate()),120)
0
这个最少应该知道 哪个字段是必须不能相同的。才好删除
0
假如字段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
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
*/
其中，在原group by 部分添加convert(nvarchar(11),日期,120)即可。
0
同一个相同信息？是某个字段内容相同还是记录相同。
0
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
