请问一个sql，哈_Sql Server

请教一个sql，高手进哈
有表A
id station_name
1 南京
2 镇江
3 丹阳
4 常州
5 无锡
6 苏州
7 上海
有表B
train_no station_name num
G7001 南京 287
G7001 常州 391
G7001 无锡 485
G7001 苏州 576
G7001 上海 576
G7003 南京 342
G7003 苏州 407
G7003 上海 407
产生表c

id station_name code num
1 南京 G7001 287
2 镇江 G7001 287
3 丹阳 G7001 1000
4 常州 G7001 391
5 无锡 G7001 485
6 苏州 G7001 576
7 上海 G7001 576
1 南京 G7003 342
2 镇江 G7003 342
3 丹阳 G7003 342
4 常州 G7003 342
5 无锡 G7003 342
6 苏州 G7003 407
7 上海 G7003 407

------解决方案--------------------
3 丹阳 G7001 1000 这个是怎么来的？
------解决方案--------------------
2 镇江 G7001 287
3 丹阳 G7001 1000

2 镇江 G7003 342
3 丹阳 G7003 342

--------------
这是怎么计算出来的
------解决方案--------------------

SQL code

;WITH t1AS(SELECT a.ID,a.train_no,a.station_name,b.numFROM (SELECT a.*,b.train_no FROM A,(SELECT DISTINCT train_no FROM B) AS B) AS aLEFT JOIN B ON a.train_no=b.train_no AND a.station_name=b.station_name)SELECT     a.train_no,a.station_name,ISNULL(a.num,b.num) AS numFROM t1 AS aCROSS APPLY(SELECT TOP 1 num FROM t1 WHERE ID<a.ID AND num IS NOT null ORDER BY ID desc) AS b
------解决方案--------------------
SQL code--> 测试数据：[ta]IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]GO CREATE TABLE [ta]([id] INT,[station_name] VARCHAR(4))INSERT [ta]SELECT 1,'南京' UNION ALLSELECT 2,'镇江' UNION ALLSELECT 3,'丹阳' UNION ALLSELECT 4,'常州' UNION ALLSELECT 5,'无锡' UNION ALLSELECT 6,'苏州' UNION ALLSELECT 7,'上海'--------------开始查询--------------------------SELECT * FROM [ta]----------------结果----------------------------/* */--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([train_no] VARCHAR(5),[station_name] VARCHAR(4),[num] INT)INSERT [tb]SELECT 'G7001','南京',287 UNION ALLSELECT 'G7001','常州',391 UNION ALLSELECT 'G7001','无锡',485 UNION ALLSELECT 'G7001','苏州',576 UNION ALLSELECT 'G7001','上海',576 UNION ALLSELECT 'G7003','南京',342 UNION ALLSELECT 'G7003','苏州',407 UNION ALLSELECT 'G7003','上海',407--------------开始查询--------------------------;WITH  cteAS (     SELECT a.[id], a.[station_name], b.[train_no], c.[num]     FROM   [ta] AS a     CROSS JOIN (                  SELECT DISTINCT                          [train_no]                  FROM    [tb]                ) AS b     OUTER APPLY (                   SELECT [num] FROM tb WHERE [station_name]= a.[station_name] AND [train_no]= b.[train_no]                 ) c   )SELECT [id], [station_name], [train_no], [num] = ISNULL([num], (                                                              SELECT TOP 1 [num] FROM cte WHERE [train_no]= t.[train_no] AND [id]< t.[id]                                                            ))FROM   cte AS tORDER BY t.[train_no], t.[id]/*id          station_name train_no num----------- ------------ -------- -----------1           南京           G7001    2872           镇江           G7001    2873           丹阳           G7001    2874           常州           G7001    3915           无锡           G7001    4856           苏州           G7001    5767           上海           G7001    5761           南京           G7003    3422           镇江           G7003    3423           丹阳           G7003    3424           常州           G7003    3425           无锡           G7003    3426           苏州           G7003    4077           上海           G7003    407(14 行受影响)*/