Question

I have two tables in a master-detail relationship. The structure is more or less as follows: Master table:

MasterID, DetailID, date, ...
masterID1, detailID1, 2010/5/1, ....
masterID2, detailID1, 2008/6/14, ...
masterID3, detailID1, 2009/5/25, ...
masterID4, detailID2, 2008/7/24, ...
masterID5, detailID2, 2010/4/1, ...
masterID6, detailID4, 2008/9/16, ...

详细表格:

DetailID, ...
detailID1, ...
detailID2, ...
detailID3, ...
detailID4, ...

I need to get all the records from the details table plus the LAST record from the master table (last by the date in the master table). Like this:

detailID1, masterID1, 2010/5/1, ....
detailID2, masterID5, 2010/4/1, ...
detailID3, null, null, ...
detailID4, masterID6, 2008/9/16, ...

I have no idea how to do this. Can anybody help me? Thanks a lot. Jan

Answer 1

Select ....
From Details As D
    Left Join   (
                Select M1.MasterId, M.DetailId, M.Date...
                From Master As M1
                Where MasterId  = (
                                    Select Max(M2.MasterId)
                                    From Master As M2
                                    Where M2.DetailId = M1.DetailId
                                    Group By M2.DetailId
                                    Having Max(M2.Date) = M1.Date
                                    )
                ) As M
        On M.DetailId = D.DetailId

我在这里做的是处理联系问题。如果你有两个总行,有相同的尾声和日期,我选择一个拥有最高硕士学位的人。

以前,如果你有共同的表态的话,这个问题就非常简单。如果有一位专家,你可以做这样的事情:

With LastMasterRows As
                (
                Select MasterId, DetailId, Date
                    , Row_Number() Over( Partition By DetailId Order By Date, MasterId ) As ItemRank
                From Master
                )
Select ...
From Details As D
    Left Join LastMasterRows As M
        On M.DetailId = D.DetailId
            And M.ItemRank = 1

友情链接