数据库的样子
2001 100
2002 NULL
2003 150
2004 NULL
2005 NULL
2006 200取值的结果
2001 100
2002 100
2003 150
2004 150
2005 150
2006 200
2001 100
2002 NULL
2003 150
2004 NULL
2005 NULL
2006 200取值的结果
2001 100
2002 100
2003 150
2004 150
2005 150
2006 200
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([date] INT,[col2] INT)
INSERT #tb
SELECT 2001,100 UNION ALL
SELECT 2002,NULL UNION ALL
SELECT 2003,150 UNION ALL
SELECT 2004,NULL UNION ALL
SELECT 2005,NULL UNION ALL
SELECT 2006,200
--------------开始查询--------------------------SELECT [date],[col2]=(SELECT MAX([col2]) FROM #tb WHERE [date]<=t.[date]) FROM #tb AS t
----------------结果----------------------------
/*
* date col2
2001 100
2002 100
2003 150
2004 150
2005 150
2006 200
*/
from tb as b