多表联结查询怎样得到重复f01Id数据的最后一条记录

select  a.f01Id,a.costCenter,a.acctPeriod,a.amt,b.status
from db.fr1 a,db.f01 b ,db.fb2 c,db.fb1 d
where a.f01Id=b.f01Id
and b.fb2Id=c.fb2Id
and c.fb1Id=d.fb1Id
and a.compId= 'A1001'
and  a.acctPeriod <='2007-11'
and b.status in ('A','B','C') 
order by d.SortNo,c.AssetNo,b.asSer

其中查询出来的数据如
1001 W1009 2001-06 2000 A
1001 W1006 2003-10 5000 A
1001 W1009 2005-11 9000 A

1002 W1005 2001-06 2000 C
1002 W1006 2004-05 5000 C
1002 W1008 2006-11 8000 C

1005 W1009 2002-06 2000 B
1005 W1011 2007-10 5000 B
1005 W1088 2007-11 9600 B
在DB2中,我想要得到如下的数据怎么办?(不用程序处理,因为数据量太大(15 万条记录)) 就是我想取得每一个a.f01Id号的的最后一条记录,在数据库中直接处理.我用distinct了,但是联表查询不行,怎么办? 请教一下各位!谢谢,谢谢!
1001 W1009 2005-11 9000 A
1002 W1008 2006-11 8000 C
1005 W1088 2007-11 9600 B




[size=18][/size][color=blue][/color]

评论

4 楼 javaOpen 2008-10-30  

   不好描述,所以没有给你邮件. 你能看懂这条SQL,我想你的问题应该可以解决了.呵呵...

3 楼 javaOpen 2008-10-30  

能看懂下面的SQL,再我们公司有常见.呵呵....
select m.*, n.remark, n.amt, n.drCr, n.qty, n.voucherNo,n.voucherDate, n.startDate, n.endDate from (
select a.*, c.accoCodeNo,c.accoCodeName, c.fib1ID,c.balanceWay from DRfi06 a
    inner join DRfib2 b on b.fib2id = a.fib2id
    inner join DRfib1 c on c.fib1id = b.fib1id
    and a.fia1ID = 'af72162ff4b8934fadc1da4a47efe26a'
    and a.accoPeriod >= '2008-07' and a.accoPeriod <= '2008-08'
    and a.levelNum >= 1 and a.levelNum <= 4
    and c.accoCodeNo >= '2121' and c.accoCodeNo <= '2121.01.01'
    and( a.debitTotal <> 0.0 or a.creditTotal <> 0.0 or a.periodEndAmt <> 0.0)
    ) as m
           left outer join (
            select a.remark, a.amt, a.drCr, a.qty, b.voucherDate, b.voucherNo, c.fia2id, c.startDate, c.endDate, d.fib2id
              from DRfi02 a inner join DRfi01 b on a.fi01id = b.fi01id
                                        inner join DRfia2 c on c.fia2id = b.fia2id 
                                        inner join DRfib2 d on d.fib2id = a.fib2id
                                        inner join DRfib1 e on e.fib1id = d.fib1id
                                               and d.levelNum >= 1 and d.levelNum <= 4
                                               and e.accoCodeNo >= '2121' and e.accoCodeNo <= '2121~'
                                               and d.status = 'N' and b.status <> 'Z'
                                      ) as n on m.fib2id = n.fib2id and m.fib5id = n.fia2id
          order by m.accoCodeNo, m.accoPeriod, n.voucherDate, n.voucherNo

2 楼 linyan 2008-10-27  

我也正好遇到这个问题，你可不可以把你的解决思路也告诉我啊，谢谢你，请发到我的邮箱好吗？[e-mail] 万分感谢

1 楼 javaOpen 2007-11-26  

问题已经解决!哈哈...