sql—从每个员工的数据中获取前1名

7cwmlq89 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(254)

我有一个查询，它生成这样的数据

select c.Id, a.userId as Payroll, c.CurrentLocation, c.Longitude, c.Latitude, c.TextOtherLocation, c.CreationDate
    from PeopleDB.dbo.masterpeople a
    inner join PeopleDB.dbo.masterPeople b on a.manager = b.userid
    inner join PeopleTracking c on a.userId = c.payroll
    where a.Manager = '20090036'
ORDER BY Id DESC

Id  Payroll     CurrentLocation       Longitude     Latitude    TextOtherLocation   CreationDate
51  20180002    Field Work Location   107.5588565   -6.9077868                      6/13/2020 19:56
50  20180002    Field Work Location   107.5588565   -6.9077868                      6/14/2020 19:56
49  20190026    Office                107.5587918   -6.9077061                      6/15/2020 19:56
48  20190026    Field Work Location   107.5588565   -6.9077868                      6/16/2020 19:56
47  20190026    Office                107.5588565   -6.9077868                      6/17/2020 19:56

如何从上述数据中获得任何工资单的排名前1的数据，我想要的是：

Id  Payroll     CurrentLocation       Longitude     Latitude    TextOtherLocation   CreationDate
51  20180002    Field Work Location   107.5588565   -6.9077868                      6/13/2020 19:56
49  20190026    Office                107.5587918   -6.9077061                      6/15/2020 19:56

谢谢你的帮助。

sql sql-server sql-server-2012 sql-server-2017

来源：https://stackoverflow.com/questions/62379824/get-top-1-from-data-for-every-employee

2条答案

按热度按时间

h22fl7wq1#

使用 row_number() :

select t.*
from (select c.Id, a.userId as Payroll, c.CurrentLocation, c.Longitude, c.Latitude, c.TextOtherLocation, c.CreationDate,
             row_number() over (partition by a.userid order by c.CreatedDate desc) as seqnum
      from PeopleDB.dbo.masterpeople a join
           PeopleDB.dbo.masterPeople b
           on a.manager = b.userid join
           PeopleTracking c
           on a.userId = c.payroll
      where a.Manager = '20090036'
     ) t
where seqnum = 1
ORDER BY Id DESC;

请注意 a , b ，和 c 对于表别名来说是非常糟糕的选择。这些应该是表格缩写，分别类似于 mp1 , mp2 以及 pt .

赞(0）回复(0）举报 2021-07-26

ztyzrc3y2#

我通常使用 row_number() 窗口函数为这样的行分配序数。

select Id,Payroll,CurrentLocation,Longitude,Latitude, TextOtherLocation, CreationDate
from (
select c.Id, a.userId as Payroll, c.CurrentLocation, c.Longitude, c.Latitude, c.TextOtherLocation, c.CreationDate, row_number() over (partition by a.userId order by c.Id desc) as PayrollRowNum
    from PeopleDB.dbo.masterpeople a
    inner join PeopleDB.dbo.masterPeople b on a.manager = b.userid
    inner join PeopleTracking c on a.userId = c.payroll
    where a.Manager = '20090036') as PayrollData where PayrollRowNum = 1

这将为每一行指定原始编号，并为每一行重置编号 a.userId . 然后根据降序只检索“第一个” c.Id 在每个 a.userId “集团”。

赞(0）回复(0）举报 2021-07-26

我来回答

sql—从每个员工的数据中获取前1名

2条答案

相关问题

热门标签

最新问答