从联合查询中选择最新数据的前1名 [英] Selecting Top 1 of latest data from joined query

问题描述

这是我的查询

SELECT  u.UserID ,
        A.Username ,
        A.Password ,
        A.CreatedOn
FROM    dbo.tbl_User U
        INNER JOIN dbo.tbl_UserAuthDetail A ON A.UserID = U.UserID
                                               AND A.CreatedOn IN (
                                               SELECT TOP 1
                                                        CreatedOn
                                               FROM     dbo.tbl_UserAuthDetail
                                               WHERE    A.UserID = U.UserID
                                               ORDER BY CreatedOn DESC )

它将返回

但是我在桌子上有这个

but i have this on my table

因此，我希望会有其他条目来生成

so i am expecting that there is additional entry to result

如果我哪里出了问题，有人可以指导我吗?并且如果可能的话，请帮助我也将其转换为LINQ.

can someone guide me if where did i go wrong? and if possible, help me also converting that to LINQ..

对不起，我的解释不好..:)

Sorry for my bad explaination.. :)

TIA

推荐答案

子查询的where语句有些混乱:

Something is fishy with your subquery's where statement:

FROM     dbo.tbl_UserAuthDetail
WHERE    A.UserID = U.UserID

A和U都在子查询之外，因此此子句完全不会限制子查询.

Both A and U are outside the subquery, so this clause will not limit the subquery at all.

尝试类似的东西:

FROM     dbo.tbl_UserAuthDetail A2
WHERE    A2.UserID = U.UserID

---

作为替代解决方案，SQL Server具有窗口功能:

---

As an alternative solution, SQL Server has window functions:

select  *
from    (
        select  row_number() over (
                    partition by u.UserID
                    order by a.createdon desc) rn
        ,       *
        from    dbo.tbl_user u
        join    dbo.tbl_userauthdetail a
        on      a.userid = u.userid
        ) as SubQueryAlias
 where  rn = 1 -- Only latest authdetail per user

或者您可以使用cross apply:

select  *
from    dbo.tbl_user u
cross apply
        (
        select  top 1 *
        from    dbo.tbl_userauthdetail a
        where   a.userid = u.userid
        order by
                a.createdon desc
        ) a

这篇关于从联合查询中选择最新数据的前1名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！