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

idstation_namecodenum
1南京G7001287
2镇江G7001287
3丹阳G70011000
4常州G7001391
5无锡G7001485
6苏州G7001576
7上海G7001576
1南京G7003342
2镇江G7003342
3丹阳G7003342
4常州G7003342
5无锡G7003342
6苏州G7003407
7上海G7003407


[解决办法]
3 丹阳 G7001 1000 这个是怎么来的？
[解决办法]
2镇江G7001287
3丹阳G70011000

2镇江G7003342
3丹阳G7003342

--------------
这是怎么计算出来的
[解决办法]

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 行受影响)*/