delete T from 表 T where exists( select 1 from 表 where A=T.A and B>T.B)
有一张表,有A,B2个列,其中A是nvarchar,B列是datetime 现在表中有数据若干,有A数据相同,但B(时间)不同的。 想把所有A数据相同的几条数据中留下B(时间)最晚的一条。 求一句SQL语言搞定delete from 表 s where exists(select 1 from 表 where A=s.A and B>s.B)
create table #A (A nvarchar(100), B datetime) insert into #A select 'aaa', getdate() insert into #A select 'bbb', getdate() insert into #A select 'ccc', getdate() insert into #A select 'ccc', getdate()-11 insert into #A select 'ccc', getdate()-12 insert into #A select 'ccc', getdate()-13 insert into #A select 'ccc', getdate()-20select max(a.B) AS timeD,a.A from #A a inner join #A b on b.A = a.A group by a.A having count(a.A) > 2
where exists(
select 1 from 表 where A=T.A and B>T.B)
现在表中有数据若干,有A数据相同,但B(时间)不同的。
想把所有A数据相同的几条数据中留下B(时间)最晚的一条。
求一句SQL语言搞定delete from 表 s
where exists(select 1 from 表
where A=s.A and B>s.B)
insert into #A select 'aaa', getdate()
insert into #A select 'bbb', getdate()
insert into #A select 'ccc', getdate()
insert into #A select 'ccc', getdate()-11
insert into #A select 'ccc', getdate()-12
insert into #A select 'ccc', getdate()-13
insert into #A select 'ccc', getdate()-20select max(a.B) AS timeD,a.A from #A a
inner join #A b on b.A = a.A
group by a.A
having count(a.A) > 2