从联合查询中选择最新数据的前1名 [英] Selecting Top 1 of latest data from joined query
本文介绍了从联合查询中选择最新数据的前1名的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的查询
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屋!
查看全文