SQL SEVRER如何给空值填上跟上一行一样的值
订单 订单明细 数量
201101 20110101 1
20110102 2
201102 20110201 1
20110202 3
类似这样的订单这一列有的值为空，但是计算的时候需要按上面的值，需要订单都填上
各位大虾门都来看看哈！
[解决办法]
with TB as(select *,row_number() over(order by getdate()) as rowid from Tablename
select isnull(订单,(select top 1 订单 from TB where not 订单 is null and rowid<a.rowid order by rowid desc )) as 订单,订单明细,数量
from TB as a
[解决办法]
--CREATE TABLE huang(订单 INT, 订单明细 int, 数量 int )
--INSERT INTO huang
--SELECT 201101, 20110101 , 1
--UNION ALL
--SELECT NULL ,20110102, 2
--UNION ALL
--SELECT 201102, 20110201 , 1
--UNION ALL
--SELECT NULL ,20110202 , 3
WITH cte
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY GETDATE() ) id ,
*
FROM Huang
),
cte1
AS ( SELECT *
FROM cte
WHERE id = 1
UNION ALL
SELECT b.id ,
CASE WHEN b.订单 IS NULL THEN a.订单
ELSE b.订单
END 订单 ,
b.订单明细 ,
b.数量
FROM cte1 a
INNER JOIN cte b ON a.id = b.id - 1
)
SELECT 订单, 订单明细 , 数量
FROM cte1
/*
订单 订单明细 数量
----------- ----------- -----------
201101 20110101 1
201101 20110102 2
201102 20110201 1
201102 20110202 3
(4 行受影响)
*/
--测试数据
if OBJECT_ID('Test_20121204') is not null drop table Test_20121204
go
CREATE TABLE Test_20121204(订单 int, 订单明细 int, 数量 int )
INSERT INTO Test_20121204
SELECT 201101,20110101,1 UNION ALL
SELECT NULL,20110102,2UNION ALL
SELECT 201102,20110201,1 UNION ALL
SELECT NULL ,20110202 ,3
--查询
;with T as(
select ROW_NUMBER() over(order by getdate())id,* from Test_20121204
)
select isnull(t1.订单,t2.订单)订单,t1.订单明细,t1.数量 from T t1 left join T t2
on t1.id=t2.id+1
--查询结果
/*
订单 订单明细 数量
----------- ----------- -----------
201101 20110101 1
201101 20110102 2
201102 20110201 1
201102 20110202 3
(4 行受影响)
*/
--删除测试数据
drop table Test_20121204
