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