sql - T-SQL 获取最后一个日期时间记录

Question

我的表格是这样的:

+---------+------------------------+-------+---------+---------+
|channel  |date                    |code   |comment  |order_id |
+---------+------------------------+-------+---------+---------+
|1        |2017-10-27 12:04:45.397 |2      |comm1    |1        |
|1        |2017-10-27 12:14:20.997 |1      |comm2    |1        |
|2        |2017-10-27 12:20:59.407 |3      |comm3    |1        |
|2        |2017-10-27 13:14:20.997 |1      |comm4    |1        |
|3        |2017-10-27 12:20:59.407 |2      |comm5    |1        |
|3        |2017-10-27 14:20:59.407 |1      |comm6    |1        |
+---------+------------------------+-------+---------+---------+

我期望这样的结果:

+---------+------------------------+-------+---------+
|channel  |date                    |code   |comment  |
+---------+------------------------+-------+---------+
|1        |2017-10-27 12:14:20.997 |1      |comm2    |
|2        |2017-10-27 13:14:20.997 |1      |comm4    |
|3        |2017-10-27 14:20:59.407 |1      |comm6    |
+---------+------------------------+-------+---------+

始终有 1 条记录，其中 order_id = x 和每个 channel 的最大日期。 channel 总数是恒定的。我的查询有效，但随着表的增长，我担心性能。执行三个几乎相同的查询似乎并不明智。

select
    *
from
    (select top(1)
        channel,
        date,
        code,
        comment
    from
        status
    where
        channel = 1 and
        order_id = 1 and
        cast(date as date) = '2017-10-27'
    order by 
        date desc) channel1
union
select 
    *
from
    (select top(1)
        channel,
        date,
        code,
        comment
    from
        status
    where
        channel = 2 and
        order_id = 1 and
        cast(date as date) = '2017-10-27'
    order by 
        date desc) channel2
union
select 
    *
from
    (select top(1)
        channel,
        date,
        code,
        comment
    from
        status
    where
        channel = 3 and
        order_id = 1 and
        cast(date as date) = '2017-10-27'
    order by 
        date desc) channel3

我该如何改进？

Answer 1

另一种选择是使用 WITH TIES 子句。没有子查询或额外字段。

Select top 1 with ties *
 From  YourTable
 Order By Row_Number() over (Partition By channel order by date desc)