如何跟踪sql记录中的最新修改？

debugcn 发表于 Dev

罂粟

我有一个表格来跟踪学生的详细信息，还有另一个表格来跟踪学生的表现。

+==========================================+
| ID      |  Department     | Date         |
+==========================================+
| 001     | English         | Jan 3 2017   |
| 001     | English         | Feb 24 2017  |
| 001     | Science         | Mar 1 2017   |
| 001     | Maths           | Mar 2 2017   |
| 001     | Maths           | Mar 21 2017  |
| 001     | Maths           | Apr 2 2017   |
| 001     | English         | Apr 7 2017   |
| 002     | Maths           | Feb 1 2017   |
| 002     | Maths           | Apr 7 2017   |
| 003     | Maths           | Apr 3 2017   |
| 003     | Maths           | Apr 7 2017   |
| 004     | Science         | Feb 1 2017   |
| 004     | Science         | Mar 1 2017   |
| 004     | Maths           | Apr 7 2017   |
| 004     | English         | Apr 9 2017   |
+==========================================+

性能表：

+===========================================================================+
| ID      |  Department         | Best score|  Avg score    |   Date        |
+===========================================================================+
| 001     | English             |   98      |   85          | Jan 30 2017   |
| 001     | English             |   89      |   80.2        | Apr 14 2017   |
| 001     | Science             |   75      |   79.8        | May 1 2017    |
| 001     | Maths               |   88      |   80.2        | Jan 12 2017   |
| 001     | Maths               |   79      |   75.6        | Feb 21 2017   |
| 001     | Maths               |   90      |   80.5        | Jan 20 2017   |
| 001     | English             |   80      |   79.3        | Mar 27 2017   |
| 002     | Maths               |   90      |   78.4        | Mar 31 2017   |
| 002     | Maths               |   85      |   80.2        | May 7 2017    |
| 003     | Maths               |   75      |   79.1        | Apr 30 2017   |
| 003     | Maths               |   80      |   80.0        | Feb 7 2017    |
| 004     | Science             |   60      |   70.3        | May 1 2017    |
| 004     | Science             |   72      |   69.9        | Mar 10 2017   |
| 004     | Maths               |   70      |   66.8        | Jan 17 2017   |
| 004     | English             |   65      |   65.0        | Mar 29 2017   |
+===========================================================================+

每当学生表中发生部门变化时，我想获得学生的最新表现和平均分数。考虑到学生 001，学生部门的变化是

| 001     | English         | Jan 3 2017   |
| 001     | Science         | Mar 1 2017   |
| 001     | Maths           | Apr 2 2017   |

对于 2017 年 1 月 3 日，没有比 Performance 表中的日期晚的日期。

2017 年 3 月 1 日，性能表中的最新记录日期为 2017 年 2 月 21 日

2017 年 4 月 2 日，性能表中的最近记录日期为 2017 年 3 月 27 日

请帮我做这件事。

库马尔·哈什

如果我的脚本有误，请稍微解释清楚

希望你解释的输出是正确的。因为我对输出有疑问。

最重要的是希望您发布了完全相同的表结构。

由于您的表结构，使用了比预期更多的窗口函数，这可能会影响您的性能。

关于您的真实需求以及表结构应该是什么以及应该如何填充，这是非常不同和重要的讨论。

用各种示例数据试试这个脚本，让我知道，

declare @StudentDetails table(ID varchar(20)
,Department varchar(20),Dates Date)
insert into @StudentDetails VALUES
('001','English','Jan 3 2017 ') 
,('001','English','Feb 24 2017') 
,('001','Science','Mar 1 2017 ') 
,('001','Maths','Mar 2 2017 ') 
,('001','Maths','Mar 21 2017') 
,('001','Maths','Apr 2 2017 ') 
,('001','English','Apr 7 2017 ') 
,('002','Maths','Feb 1 2017 ') 
,('002','Maths','Apr 7 2017 ') 
,('003','Maths','Apr 3 2017 ') 
,('003','Maths','Apr 7 2017 ') 
,('004','Science','Feb 1 2017 ') 
,('004','Science','Mar 1 2017 ') 
,('004','Maths','Apr 7 2017 ') 
,('004','English','Apr 9 2017 ') 
--select * from @StudentDetails

declare @Performance table( ID varchar(20)
,Department varchar(20),Bestscore float,Avgscore float,PDate date)

insert into @Performance VALUES
 ('001','English',98,85    ,'Jan 30 2017')
,('001','English',89,80.2  ,'Apr 14 2017')
,('001','Science',75,79.8  ,'May 1 2017 ')
,('001','Maths',88,80.2  ,'Jan 12 2017')
,('001','Maths',79,75.6  ,'Feb 21 2017')
,('001','Maths',90,80.5  ,'Jan 20 2017')
,('001','English',80,79.3  ,'Mar 27 2017')
,('002','Maths',90,78.4  ,'Mar 31 2017')
,('002','Maths',85,80.2  ,'May 7 2017 ')
,('003','Maths',75,79.1  ,'Apr 30 2017')
,('003','Maths',80,80.0  ,'Feb 7 2017 ')
,('004','Science',60,70.3  ,'May 1 2017 ')
,('004','Science',72,69.9  ,'Mar 10 2017')
,('004','Maths',70,66.8  ,'Jan 17 2017')
,('004','English',65,65.0  ,'Mar 29 2017')
--select * from @Performance
--declare @SID  varchar(20)='001'

;with CTE as
(
select *
,ROW_NUMBER()over(partition by id order by Dates,Department) rn
from @StudentDetails
--where id=@SID
)
,CTE3 AS(
select c.id, c.Department,c.dates,c.rn,1 rn3
from cte c
where rn=1

union ALL

select c.id, c.Department,c.dates,c.rn
,case when c.Department=c3.Department  and c.dates>c3.dates 
then cast(c3.rn3 as int)
else cast(c3.rn3+1 as int) end
from cte c
inner join cte3 c3
on c.id=c3.id
where c.rn=c3.rn+1
and c.rn<=7
)
,cte4 AS(
select *,0 rn1 from cte3 where rn=1
union ALL
select * from 
(
select * ,ROW_NUMBER()over(PARTITION by id,rn3 order by dates desc) rn1
from cte3
where rn3>1
)t4 where t4.rn1=1
)

select c.id,c.department,c.dates,fn.Avgscore AVGScroe,fn.pdate RecentPDate 
from cte4 c
OUTER apply(
select * from
(select p.*,ROW_NUMBER()over( order by pdate desc)rn2 
from @Performance P
where c.id=p.id and p.pDate<c.dates)t4
where rn2=1 )fn
order by c.id