id,dep,date1,date2select distinct * from
表 表1
where not exists(select 1 from 表 表2 where 表2.id = 表1.id and 表2.dep = 表1.dep
and 表2.date1 > 表1.date1 and 表2.date2 > 表1.date2)--似乎不用group by
表 表1
where not exists(select 1 from 表 表2 where 表2.id = 表1.id and 表2.dep = 表1.dep
and 表2.date1 > 表1.date1 and 表2.date2 > 表1.date2)--似乎不用group by
from table1 A
join (
select id
,dep
,max(date1) as 'date1'
from table1
group by id,dep
)B on A.id=B.id
and A.dep=B.dep
and A.date1=B.date1
select A.id,A.dep,A.date1,A.date2
from table1 A
join (
select id
,dep
,max(date1) as 'date1'
from table1
group by id,dep
)B on A.id=B.id
and A.dep=B.dep
and A.date1=B.date1
1,it,05-08-08,05-08-22
2, it,05-08-02,05-08-22
当然表中还有其它字段,我也需要用。
这样取出后有两条记录,无法达到我想要的1,it,05-08-08,05-08-22这条记录.
1,it,05-08-22,05-08-08
2, it,05-08-22,05-08-02
当然表中还有其它字段,我也需要用。
这样取出后有两条记录,无法达到我想要的1,it,05-08-22,05-08-08这条记录.
insert into @TempTable
select 1,'it','2005-01-01','2005-01-01' union
select 1,'it','2005-01-01','2005-01-01' union
select 1,'it','2005-02-01','2005-02-01' union
select 2,'it','2005-01-01','2005-01-01' union
select 2,'it','2005-01-01','2005-01-01' union
select 3,'it','2005-01-01','2005-01-01'
select distinct * from @TempTable tb1
where not exists(select 1 from @TempTable tb2 where tb2.id = tb1.id and tb2.dep = tb1.dep and tb2.date1 > tb1.date1 and tb2.date2 > tb1.date2)