select num,ITEM,min(effect_date),min(你所想要查询的字段) from A a where (select count(*) from A where num=a.num and ITEM=a.ITEM and effect_date>a.effect_date)<1 --生效日期(effect_data)最晚的哪个记录. group by num,ITEM
select * from 表A a, (select max(effect_data) as effect_data,num,ITEM from 表A group by num+ITEM) b where a.effect_date=b.effect_date and a.num=b.num and a.ITEM=b.ITEM
select distinct a.* from 表A a, (select max(effect_data) as effect_data,num,ITEM from 表A group by num+ITEM) b where a.effect_date=b.effect_date and a.num=b.num and a.ITEM=b.ITEM
select num,ITEM,min(effect_date),min(你所想要查询的字段) from A a
where (select count(*) from A where num=a.num and ITEM=a.ITEM and effect_date>a.effect_date)<1 --生效日期(effect_data)最晚的哪个记录.
group by num,ITEM
(select max(effect_data) as effect_data,num,ITEM from 表A group by num+ITEM) b
where a.effect_date=b.effect_date and a.num=b.num and a.ITEM=b.ITEM
(select max(effect_data) as effect_data,num,ITEM from 表A group by num+ITEM) b
where a.effect_date=b.effect_date and a.num=b.num and a.ITEM=b.ITEM