如何使用JOIN从表中获取最新行

I have a join of 5 tables to get the tasks posted by each member having about 15 columns. But for sample code I have taken just two tables of them.

我有5个表的连接，以获得每个成员发布的约有15列的任务。但是对于示例代码，我只使用了两个表。

SELECT TOP 5 
    dbo.MemberMst.MemberID, dbo.MemberMst.fname, 
    dbo.TaskMst.TaskMstID, dbo.TaskMst.OnDate, dbo.TaskMst.Description
FROM   
    dbo.MemberMst 
LEFT JOIN
    dbo.TaskMst ON dbo.MemberMst.MemberID = dbo.TaskMst.MemberID

Output is:

输出是：

MemberID fname  TaskMstID    OnDate                   Description
3    Ursula     NULL         NULL                      NULL
84   Opeyemi    30           2012-09-18 00:00:00.000     asd
85   test       21           2012-09-18 10:30:46.900     aaa
85   test       22           2012-09-18 10:31:04.967     eeee
85   test       23           2012-09-18 10:31:26.640     vvvv

Here in above query I get 3 rows for MemberID=85 who posted 3 tasks but I need only one task from that member which is the latest. How to get the latest task posted by a member so that result would be:-

在上面的查询中，我获得了3行，其中MemberID = 85，他们发布了3个任务，但我只需要来自该成员的一个最新任务。如何获取成员发布的最新任务，以便结果如下： -

MemberID fname  TaskMstID OnDate                Description
3    Ursula     NULL      NULL                   NULL
84   Opeyemi    30        2012-09-18 00:00:00.000   asd
85   test       23        2012-09-18 10:31:26.640   vvvv

I mean to say just only one record for each memberID having tasks?

我的意思是说每个有任务的memberID只有一条记录？

Help appreciated..!

帮助赞赏..！

Thanks in advance...!

提前致谢...！

4 个解决方案

#1

Since you are using SQL Server 2008, you can take advantage of Window Functions.

由于您使用的是SQL Server 2008，因此可以利用窗口函数。

Try something like this:

尝试这样的事情：

SELECT  c.MemberID, c.fname, c.TaskMstID, c.OnDate, c.Description
FROM
    (
        SELECT  a.MemberID, a.fname,
                b.TaskMstID, b.OnDate, b.Description,
                ROW_NUMBER() OVER (Partition BY a.MemberID ORDER BY b.OnDate DESC) RN
        FROM    MemberMst a
                LEFT JOIN TaskMst b
                    ON a.MemberID = b.MemberID
    ) c
WHERE c.RN = 1

#2

;With Cte1  AS
(

SELECT  top 5 dbo.MemberMst.MemberID, dbo.MemberMst.fname, dbo.TaskMst.TaskMstID, dbo.TaskMst.OnDate, dbo.TaskMst.Description
FROM   dbo.MemberMst left JOIN
dbo.TaskMst ON dbo.MemberMst.MemberID = dbo.TaskMst.MemberID
),
Cte2 As
(   SELECT Rn = Row_Number() Over(Partition by MemberId Order By OnDate Desc), *
    From Cte1

)
Select * 
From Cte2 
Where Rn = 1

Should do the task for you. Try it out.

应该为你完成任务。试试看。

#3

You can partition by MemberID, then take a Rank over it and select only the top ranked item.

您可以按MemberID进行分区，然后对其进行排名，并仅选择排名靠前的项目。

More info on MSDN

有关MSDN的更多信息

Try some thing like this

尝试这样的事情

WITH MyCte AS 
(SELECT  top 5 dbo.MemberMst.MemberID, dbo.MemberMst.fname, dbo.TaskMst.TaskMstID, dbo.TaskMst.OnDate, dbo.TaskMst.Description,
        ROW_NUMBER() OVER (PARTITION BY dbo.MemberMst.MemberID ORDER BY dbo.TaskMst.OnDate DESC) AS RowNum 
FROM   dbo.MemberMst 
left JOIN dbo.TaskMst ON dbo.MemberMst.MemberID = dbo.TaskMst.MemberID)
SELECT  *
FROM    MyCte
WHERE   RowNum > 0

UPDATE: Changed to ROW_NUMBER() instead of RANK() to avoid the problem of multiple rows with same rank.

更新：更改为ROW_NUMBER（）而不是RANK（）以避免具有相同排名的多行问题。

#4

You Just have to USE Group BY() Clause with MAX() Function

您只需使用MAX（）函数使用Group BY（）子句

with CTE as(
    SELECT TOP 5 
        dbo.MemberMst.MemberID, 
        dbo.MemberMst.fname, 
        dbo.TaskMst.TaskMstID, 
        dbo.TaskMst.OnDate, 
        dbo.TaskMst.Description
    FROM
        dbo.MemberMst left 
        JOIN dbo.TaskMst 
        ON dbo.MemberMst.MemberID = dbo.TaskMst.MemberID)

select dbo.MemberMst.MemberID, 
        dbo.MemberMst.fname, 
        dbo.TaskMst.TaskMstID, 
        max(dbo.TaskMst.OnDate) as OnDate, 
        dbo.TaskMst.Description
from CTE
group by MemberID

#1