有表 H
字段 NUMBER DATE
1 12345 2009-02-20 15:55:42.000
2 12345 2008-01-20 16:35:12.000
3 54321 2009-03-20 06:43:51.000
4 54321 2007-12-09 14:15:43.000
请问如何删除 DATE 较早的那个记录
例从记录1,2中删除记录2
从记录3,4中删除记录4
字段 NUMBER DATE
1 12345 2009-02-20 15:55:42.000
2 12345 2008-01-20 16:35:12.000
3 54321 2009-03-20 06:43:51.000
4 54321 2007-12-09 14:15:43.000
请问如何删除 DATE 较早的那个记录
例从记录1,2中删除记录2
从记录3,4中删除记录4
解决方案 »
- 取得唯一的前四条记录,SQL语句如何写!
- 做一查询系统,如何存储用户的图片?
- 急求下面的SQL?先谢谢大家帮忙!!!
- 有哪个存储过程可以查看数据库有哪些表啊?
- 请问如何快速的对对有2000万记录的表进行数据查询??
- SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. error code 0xC0202009
- 添加到二叉树的问题,请高手赐招,急
- 如何编程实现读取sql服务器下的文件目录(不是数据库目录),其中sql服务器不是本机。
- 用一条sql将两表内容(结构基本相同)纵向合并的方法?
- 关于ORACLE中事务处理的问题
- 求救!!这个还算简单的插入语句怎么写?,老板催我n次了...
- 小问题请教大家,谢谢
delete from 表H
where not exists(select 1 from 表H b where 表H.NUMBER=b.NUMBER and 表H.DATE>b.DATE)
--> 测试数据: @H
declare @H table (字段 int,NUMBER int,DATE datetime)
insert into @H
select 1,12345,'2009-02-20 15:55:42.000' union all
select 2,12345,'2008-01-20 16:35:12.000' union all
select 3,54321,'2009-03-20 06:43:51.000' union all
select 4,54321,'2007-12-09 14:15:43.000'delete @h from @h a where exists(select 1 from @h where number=a.number and date>a.date)select * from @H
from
H a,
(select NUMBER,[DATE] from H t where not exists(select 1 from H where NUMBER=t.NUMBER and [DATE]<t.[DATE]) b
where a.NUMBER=b.NUMBER and a.[DATE]=b.[DATE]
delete t from H where exists(select * from H where Entryid%2=0)
来个不一样的,删除ID字段为偶数的
WHERE NOT EXISTS(SELECT1 FROM表H b WHERE 表H.NUMBER=b.NUMBER and表H.DATE>b.DATE
DELETE FROM H
DELETE FROM H
连接删除delete H
from
(select Number, min(Date) as Date
from H
group by Number
) as v
where H.Number = v.Number and
H.Date = v.Date
delete @h from @h a where not exists(select 1 from @h where number=a.number and date<a.date)
on a.c1=b.c1 and a.c2=b.c2
on a.c1=b.c1 and a.c2=b.c2
declare @H table (字段 int,NUMBER int,DATE datetime)
insert into @H
select 1,12345,'2009-02-20 15:55:42.000' union all
select 2,12345,'2008-01-20 16:35:12.000' union all
select 3,54321,'2009-03-20 06:43:51.000' union all
select 4,54321,'2007-12-09 14:15:43.000'delete @h from @h t where 字段 = (select top 1 字段 from @h where number=t.number order by date)select * from @H
/*
字段 NUMBER DATE
----------- ----------- -----------------------
1 12345 2009-02-20 15:55:42.000
3 54321 2009-03-20 06:43:51.000(2 行受影响)
*/
delete a from tableName a where not exists (select 1 from tableName where number=a.number and and date>a.date)
create table H
(
字段 int,
NUMBER int,
DATE datetime
)
insert into H
select 1,12345,'2009-02-20 15:55:42.000' union all
select 2,12345,'2008-01-20 16:35:12.000' union all
select 3,54321,'2009-03-20 06:43:51.000' union all
select 4,54321,'2007-12-09 14:15:43.000'delete a from H a where exists(select 1 from H where number=a.number and date>a.date)select * from H/*
字段 NUMBER DATE
----------- ----------- -----------------------
1 12345 2009-02-20 15:55:42.000
3 54321 2009-03-20 06:43:51.000(2 行受影响)
*/