Need query to relate unique parent to child that i

问题:

Parent table (tblLog) has three fields
UserID
Time
Action

Child Table (tblRole) has three fields
UserID
Role
TransDate

I want the query to give me the three fields from tblLog and the Role information from tblRole for each UserID. The problem is that UserID in tblRole is not unique because the table contains historical roles. So what I need to do it only include the UserID and Role for the Max of the TransDate. My understanding is that this is straight forward but I don't know how to do it. Here is the sql that I am trying to fix:

SELECT L.[ID]
      ,L.[UserID]
      ,L.[Time]
      ,L.[Action]
      ,R.Role
  FROM [TEST111].[dbo].[tblLog] as L
  Join [TEST111].[dbo].[tblRole] as R
  On  L.[UserID] = R.[UserID]

Here is sample data from tblRole*:

ID	UserID	Role	TransDate
1	U001	Super	1/1/2015
2	U001	Super	2/15/2015
3	U001	Mgr	3/7/2015
4	U002	Line	2/10/2015
5	U002	Super	5/4/2015
6	U004	Mgr	4/4/2015
7	U005	Super	1/15/2015

*I had previously said that this was sample date from tblLog

回答1:

You can do this with a ROW_NUMBER over the TransDate for each UserId:

;With Cte As
(
    Select  L.[ID],
            L.[UserID],
            L.[Time],
            L.[Action],
            R.[Role],
            Row_Number() Over (Partition By [L].[UserId] Order By [R].[TransDate] Desc) Row_Number
    From    [TEST111].[dbo].[tblLog]    as L
    Join    [TEST111].[dbo].[tblRole]   as R    On  L.[UserID] = R.[UserID]
)
Select  [Id], [UserId], [Time], [Action], [Role]
From    Cte
Where   [Row_Number] = 1

This query will pull the most recent transaction information for each UserId.

回答2:

You need to GROUP BY the fields that you are determining the value for, then add the MAX aggregate for the value you're analyzing.

SELECT 
    L.[UserID]
    R.[Role],
    MAX(L.[TransDate]) AS [MaxDate]
FROM [TEST111].[dbo].[tblLog] L
    INNER JOIN [TEST111].[dbo].[tblRole] R
        ON L.[UserID] = R.[UserID]
GROUP BY
    L.[UserID]
    R.[Role]