我有一张表,其结构类似于以下定义的表。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 BY
withMAX()
然后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] 删除。
我来说两句