每个用户名的最大日期 [英] Max date per username

问题描述

您好，



我正在尝试获取每个用户名的最新日期。数据如下：



日期用户名金额PID

12-12-2015 Mr Y R50 5997897

15-12-2015 Y R56先生8784545

13-07-2015 J R89女士87824

04-04-2015 J R87女士875414



所以输出需要：

日期用户名金额PID

15-12-2015 Y R56先生8784545

13-07-2015女士J R89 87824



我尝试过以下方法但没有成功：

Hi there,

I am trying to get the lastest date per username. The data looks as follows:

Date Username Amount PID
12-12-2015 Mr Y R50 5997897
15-12-2015 Mr Y R56 8784545
13-07-2015 Ms J R89 87824
04-04-2015 Ms J R87 875414

So the output needs to be:
Date Username Amount PID
15-12-2015 Mr Y R56 8784545
13-07-2015 Ms J R89 87824

I have tried the following to no success:

select s1.Date, s1.UserName, s2.TradeAmount  from #ListOfTrans s1 
inner join
(
  select max(a.Date) as  Date,
    a.Amount, a.Username, a.PortfolioID
  from  #ListOfTrans a
  group by UserName,Amount,Date, PID
) s2
  on s1.UserName = s2.UserName
  and s1.Date = s2.Date
  and s1.PID = s2.PID
  group by s1.UserName, s2.Amount,s1.Date

推荐答案

陈你的查询是这样的，然后再试一次

Change your query like this and try again

select T.*
from    
(
  select a.UserName, max(a.Date) as  MAXDate
  from  #ListOfTrans a
  group by UserName
) T INNER JOIN #ListOfTrans LT ON T.UserName = LT.UserName AND T.Date = LT.MAXDATE

SQL很棒，因为有很多方法可以写东西。上面的一个会起作用，但我更喜欢下面的语法。

SQL is great, cause there are tons of ways to write something. The one above will work, but I prefer the syntax below.

SELECT (SELECT MAX(Date)
		FROM #ListOfTrans T2
		WHERE T2.UserName = T1.UserName) Date,
		UserName,
		Amount, 
		PID
FROM #ListOfTrans T1

这里需要注意的一点是，如果这个表很大，这个查询可能会变慢。您应该考虑在UserName和Date列的表上添加索引。



Hogan

One thing to note here, if this table is going to be large, this query could get slow. You should consider adding an index on the table of UserName and Date column.

Hogan

首先，使用正确的数据为每个表列的预期目的键入，例如日期 [ ^ ]而不是日期字段的varchar，这将消除任何数据错误和丑陋的转换代码。 />
其次，避免使用关键字或数据类型名称，例如'date'作为字段名称。

然后，试试这个：

First thing first, use proper data type for the intended purpose of each table column, e.g. Date[^] instead of varchar for the date field, that will eliminate any data error and ugly conversion code.
Secondly, avoid using keywords or data type name such as 'date' as field name.
Then, try this:

select * from tablename t1 where datefield = (
select max(datefield) from tablename t2 where t1.username=t2.username)

这篇关于每个用户名的最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！