sql按每个policyid的日期获取前2行，但日期不同

0lvr5msh 于 2021-07-26 发布在 Java

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

ValId | PolicyId | Date       | Value 
------+----------+------------+-------
    1 |   11     | 2020-06-01 | 2000
    2 |   11     | 2020-06-03 | 3000
    3 |   11     | 2020-06-03 | 4000
    4 |   12     | 2020-06-02 | 8000 
    5 |   12     | 2020-06-03 | 8500

我想得到最新的前2名 Val 每行 PolicyId 但它们不能来自同一个日期。
行 PolicyId = 12 正确返回-有效4和5。
为了 PolicyId = 11 ，返回具有有效2和3的行，但由于它们在同一日期，我希望返回有效1的行，而不是有效2的行。

SELECT 
    V.ValId, V.PolicyId, V.Value, V.Date
FROM
    (SELECT 
         ValId, PolicyId, Value, Date,
         ROW_NUMBER() OVER (PARTITION BY PolicyId ORDER BY Date Desc, ValId DESC) AS RowNum
     FROM 
         TVal) V 
WHERE  
    RowNum <= 2

sql sql-server tsql

来源：https://stackoverflow.com/questions/62881555/sql-getting-top-2-rows-by-date-per-policyid-but-with-distinct-dates

2条答案

按热度按时间

sirbozc51#

您可以按日期和日期内枚举行：

select t.*
from (select t.*,
             dense_rank() over (partition by policyid order by date desc valId desc) as seqnum,
             rank() over (partition by policyid, date order by valId desc) as seqnum_within_date
      from tval
     ) t
where seqnum <= 2 and seqnum_within_date = 1;

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

uqdfh47h2#

使用gordonlinoff的建议，我能够完成以下sql

Select v.* from 
(
    select t.*, 
    row_number() over (partition by policyid order by date desc valId desc) as seqnum,
    from (select t.*             
          dense_rank() over (partition by policyid, date order by valId desc) as seqnum_within_date
          from tval
         ) t where seqnum_within_date = 1
)v where seqnum <= 2

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

我来回答

sql按每个policyid的日期获取前2行，但日期不同

2条答案

相关问题

热门标签

最新问答