SQL 服务器中的 top 子句

Question

Data：

User ID       | plan           | effective date.  |  package
1.            | P1             |  24-05-2017      |  2017
1.            | P1             |  01-06-2017      |  2017
1.            | P2             |  01-07 2017      |  2017

如果计划发生更改 - 选择计划最近生效日期在上面的情况下：我按日期描述的顶部子句顺序得到它

如果计划没有改变，两者都是 p1 行，假设只有前 2 行在那里，我必须选择 25-05-2017 日期行。

尝试检查上一年的计划与最后一行相同与否。

Answer 1

听起来您希望为每个用户提供最新的包。如果您希望每个用户获得最新的包 |计划组合，只需将plan添加到partition by中...让它变得(partition by [User ID], plan order by [effective date] desc)

select
    [User ID]
    ,[plan]
    ,[effective date]
    ,[package] 
from
    (select 
        [User ID]
        ,[plan]
        ,[effective date]
        ,[package]
        ,row_number() over (partition by [User ID] order by [effective date] dec) as rn
     from YourTable) a
where rn = 1

Answer 2

您可以使用

如下所示的查询：

Select top (1) with ties * from yourtable
order by row_number() over (partition by [user id] order by [effective date] desc)