我将如何查询，以便显示每个用户的最新位置

Question

我将如何编写查询来显示每个用户的最新位置，类似于我的显示方式？我已经四处搜索，但一直未能正确实现它。

+----------+-----------+----------+-----------+-------------------------+
| Username | Firstname | Lastname | Location  |       DateandTime       |
+----------+-----------+----------+-----------+-------------------------+
| ms       | mark      | smith    | labs      | 2017-05-04 15:35:00.000 |
| jb       | james     | brown    | home      | 2017-05-04 18:55:00.000 |
| cj       | charlie   | jones    | uni       | 2017-05-04 18:56:00.000 |
| cj       | charlie   | jones    | home      | 2017-05-04 19:33:00.000 |
| ms       | mark      | smith    | shop      | 2017-05-05 15:40:00.000 |
| jb       | james     | brown    | somewhere | 2017-05-05 16:31:00.000 |
| ms       | mark      | smith    | work      | 2017-05-07 17:42:00.000 |
| ms       | mark      | smith    | market    | 2017-05-07 17:45:00.000 |
+----------+-----------+----------+-----------+-------------------------+
+----------+-----------+----------+-----------+-------------------------+
| Username | Firstname | Lastname | Location  |       DateandTime       |
+----------+-----------+----------+-----------+-------------------------+
| cj       | charlie   | jones    | home      | 2017-05-04 19:33:00.000 |
| jb       | james     | brown    | somewhere | 2017-05-05 16:31:00.000 |
| ms       | mark      | smith    | market    | 2017-05-07 17:45:00.000 |
+----------+-----------+----------+-----------+-------------------------+

Answer 1

您可以将窗口函数row_number与top 1 with ties一起使用，以获取每个用户名的最新一行。

select top 1 with ties *
from your_table
order by row_number() over (
        partition by username order by dateandtime desc
        );

Answer 2

这应该有效：

;with cte_a as
(
    select distinct username, Firstname, Lastname from dbo.mytable
)
select a.username, a.Firstname, a.Lastname, q.[Location] [Last Location], q.DateAndTime
from cte_a a
outer apply
(
    select top 1 b.[Location], b.DateAndTime
    from dbo.myTable b 
    where b.Username = a.username
    order by DateAndTime desc
)q