sql查询为每个用户找到不同的结果 [英] sql query to find distinct result for every user

问题描述

你好朋友
我在sqlserver查询中遇到问题.
我有一张桌子上有这样的记录.

hello freinds
i have a probulm in sqlserver query.
i have a table having records like this.

Bill no     userid      amt          billenterdate
1            1         100              2012-05-24 05:29:35.223
2             2          200               2012-03-24 05:29:35.223
3             2          200            2012-01-24 05:29:35.223
4              1           2000             2012-05-4 05:29:35.223

-------------------------------------------------- ------------------

现在，我想为每个用户ID选择一条记录，其中记账日期是该用户所有记录中的最大值.


结果应该是:-

--------------------------------------------------------------------

now i want to select a single record for each userid where billenter date is maximum from that users all records.


result should be:-

1       1         100    2012-05-24 05:29:35.223
2       2          200   2012-03-24 05:29:35.223

请帮助我如何实现此结果

please help me how to achieve this result

推荐答案

Select BillNo,userid, amt, billenterdate
From
(
    SELECT
        BillNo,userid, amt, billenterdate, Rank() over (Partition BY userid order by Cast(Convert(varchar,billenterdate,106) as datetime) Desc) as [Rank]
    FROM
    TblBillDetails
) A
where [Rank]=1

使用子查询和内部联接的另一种解决方案

Another solution using subquery and inner join

select * from Table_1 a 
inner join (select max(billenterdate) as billenterdate,userid from Table_1 group by userid) as userinfoTable 
on a.billenterdate = userinfoTable.billenterdate and a.userid = userinfoTable.userid order by billno

或相关的EXISTS结构

Or a correlated EXISTS structure

SELECT *
FROM   YourTable t1
WHERE  NOT EXISTS (SELECT 1
                   FROM   YourTable t2
                   WHERE  t2.UserId        = t1.UserId
                   AND    t2.BillEnterDate > t1.BillEnterDate)

这篇关于sql查询为每个用户找到不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！