select * from 表 a
where not exists (select * from 表 b where a.id=b.id and
(a.col_a<b.col_a and a.col_b<a.col_b and a.col_c<b.col_c))
where not exists (select * from 表 b where a.id=b.id and
(a.col_a<b.col_a and a.col_b<a.col_b and a.col_c<b.col_c))
from table
where id in(select max(id) as id,col_a,col_b,col_c from table group by col-a,col_b,col_c)
先根据col_a,col_b,col_c进行Group By.并且取出Min(或者Max)的时间,这样就可以确保每三个存在的col_a,col_b,col_c的组合可以查询出一条(且仅有一条)记录.
再用这个查询结果与原表进行连接,查出col_a,col_b,col_c相等并且时间也一致的记录,即可以得出所要的结果.示例语句如下:Select t.*
From
[Table_Test] t,
(
Select col_a,col_b,col_c,Min(import_date) import_date
From [Table_Test]
Group By col_a,col_b,col_c
) tGrp
Where t.col_a = tGrp.col_a And t.col_b = tGrp.col_b And t.col_c = tGrp.col_c
And DateDiff(dd,t.import_date,tGrp.import_date)
应该是:
And DateDiff(dd,t.import_date,tGrp.import_date) = 0
where import_date in (select top 1 import_date b
from tb where a.col_a=b.col_a
and a.col_b=b.col_b
and a.col_c=b.col_c
order by import_date)
From
[Table_Test] t,
(
Select col_a,col_b,col_c,Min(import_date) import_date
From [Table_Test]
Group By col_a,col_b,col_c
) tGrp
Where t.col_a = tGrp.col_a And t.col_b = tGrp.col_b And t.col_c = tGrp.col_c
And DateDiff(dd,t.import_date,tGrp.import_date)把其中的select t.* 改为delete即可,可行吗?
但是条件可能要稍微改一下.And DateDiff(dd,t.import_date,tGrp.import_date) <> 0就可以把"时间不等同于最小(或最大)时间"的记录Delete掉!
具体可以参照SQL BOL里的DateDiff的说明哦.