选择具有最新数据的月份和上个月

汤姆·纳什

我有一张表,其结构类似于以下定义的表。PK是前4列,最后一列是实际得分。每行都完全填充了该会计年度的12个月,并在该月可用时填写分数。每个月,我们要报告最近的可用分数是多少(有些分数落后一两个月,因此我们不能使用当前月份)以及上一个分数是什么。鉴于以下提供的数据,我希望结果如下所示:

+--------+--------+--------+-----------+----------+---------------+
|ScoreKey|DeptName|TestName|ScorePeriod|ScoreValue|CurrentPrevious|
+--------+--------+--------+-----------+----------+---------------+
|    1   |  'abc' |  'def' |  201707   |   1      |   Current     |
|    1   |  'abc' |  'def' |  NULL     |   NULL   |   Previous    |
|    2   |  'abc' |  'def' |  201801   |   7      |   Current     |
|    2   |  'abc' |  'def' |  201712   |   6      |   Previous    |
+--------+--------+--------+-----------+----------+---------------+

我尝试使用GROUP BYwithMAX()然后UNION过滤掉该MAX值,但这不能解释仅包含一行数据的数据(它不返回任何行)。

提供表和值的代码:

    create table #scores (
        ScoreKey INT,
        DeptName VARCHAR(10),
        TestName VARCHAR(10),
        ScorePeriod INT,
        ScoreValue INT
    )

    insert into #scores values (1,'abc','def',201707,1)
    insert into #scores values (1,'abc','def',201708,NULL)
    insert into #scores values (1,'abc','def',201709,NULL)
    insert into #scores values (1,'abc','def',201710,NULL)
    insert into #scores values (1,'abc','def',201711,NULL)
    insert into #scores values (1,'abc','def',201712,NULL)
    insert into #scores values (1,'abc','def',201801,NULL)
    insert into #scores values (1,'abc','def',201802,NULL)
    insert into #scores values (1,'abc','def',201803,NULL)
    insert into #scores values (1,'abc','def',201804,NULL)
    insert into #scores values (1,'abc','def',201805,NULL)
    insert into #scores values (1,'abc','def',201806,NULL)
    insert into #scores values (2,'abc','def',201707,1)
    insert into #scores values (2,'abc','def',201708,3)
    insert into #scores values (2,'abc','def',201709,4)
    insert into #scores values (2,'abc','def',201710,5)
    insert into #scores values (2,'abc','def',201711,5)
    insert into #scores values (2,'abc','def',201712,6)
    insert into #scores values (2,'abc','def',201801,7)
    insert into #scores values (2,'abc','def',201802,NULL)
    insert into #scores values (2,'abc','def',201803,NULL)
    insert into #scores values (2,'abc','def',201804,NULL)
    insert into #scores values (2,'abc','def',201805,NULL)
    insert into #scores values (2,'abc','def',201806,NULL)
狗仔队
select * 
from 
( select * 
       , row_number over (partition by ScoreKey, DeptName, TestName order by ScorePeriod desc) as rn
  from table 
) aa
where rn <= 2

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章