CREATE TABLE TB112602
(A VARCHAR(20),B DATETIME,C INT)INSERT INTO TB112602
SELECT 'P1','2008-07-01',1 UNION ALL
SELECT 'P2','2008-07-01',2 UNION ALL
SELECT 'P1','2008-08-01',5 UNION ALL
SELECT 'P2','2008-08-01',0 UNION ALL
SELECT 'P1','2008-09-01',0 UNION ALL
SELECT 'P2','2008-09-01',0 UNION ALL
SELECT 'P1','2008-10-01',6 UNION ALL
SELECT 'P2','2008-10-01',10 UNION ALL
SELECT 'P1','2008-11-01',0 UNION ALL
SELECT 'P2','2008-11-01',20
SELECT A,B,CASE WHEN C=0 THEN (SELECT TOP 1 C FROM TB112602 WHERE AA.B>B AND AA.A=A AND C<>0 ORDER BY B DESC ) ELSE C END
FROM TB112602 AA
/*
--result
P1 2008-07-01 00:00:00.000 1
P2 2008-07-01 00:00:00.000 2
P1 2008-08-01 00:00:00.000 5
P2 2008-08-01 00:00:00.000 2
P1 2008-09-01 00:00:00.000 5
P2 2008-09-01 00:00:00.000 2
P1 2008-10-01 00:00:00.000 6
P2 2008-10-01 00:00:00.000 10
P1 2008-11-01 00:00:00.000 6
P2 2008-11-01 00:00:00.000 20*/
(A VARCHAR(20),B DATETIME,C INT)INSERT INTO TB112602
SELECT 'P1','2008-07-01',1 UNION ALL
SELECT 'P2','2008-07-01',2 UNION ALL
SELECT 'P1','2008-08-01',5 UNION ALL
SELECT 'P2','2008-08-01',0 UNION ALL
SELECT 'P1','2008-09-01',0 UNION ALL
SELECT 'P2','2008-09-01',0 UNION ALL
SELECT 'P1','2008-10-01',6 UNION ALL
SELECT 'P2','2008-10-01',10 UNION ALL
SELECT 'P1','2008-11-01',0 UNION ALL
SELECT 'P2','2008-11-01',20
SELECT A,B,CASE WHEN C=0 THEN (SELECT TOP 1 C FROM TB112602 WHERE AA.B>B AND AA.A=A AND C<>0 ORDER BY B DESC ) ELSE C END
FROM TB112602 AA
/*
--result
P1 2008-07-01 00:00:00.000 1
P2 2008-07-01 00:00:00.000 2
P1 2008-08-01 00:00:00.000 5
P2 2008-08-01 00:00:00.000 2
P1 2008-09-01 00:00:00.000 5
P2 2008-09-01 00:00:00.000 2
P1 2008-10-01 00:00:00.000 6
P2 2008-10-01 00:00:00.000 10
P1 2008-11-01 00:00:00.000 6
P2 2008-11-01 00:00:00.000 20*/
解决方案 »
- 请教一个关于毫秒级日期格式的问题
- 请高手帮忙.MSSQL里的 ORDER BY ,GROUP BY,HAVING这三个的具体用法和分别
- 新手求问一个关于很简单的SQL语句的问题.第一次问问题很激动啊.
- SQL server语句中有没有用来运算二进制的语法呢
- 急求 sql语句
- 有没有类似ACCESS中的NOW()函数?填加记录时,自动加上日期。
- SQL Server中怎么查看每个表的大小?
- 如何使用SQL语句 将所有的作业调度生成脚本?
- 求救,这句sql如何写?
- 怎样在已有的复制中加入一个已经有数据的表(Sybase SQL anywhere)?
- 感觉有点怪的一个问题。
- 如何查询连接到SQL服务器的客户端的MAC地址
(ProductID VARCHAR(20),EnterDate DATETIME,TotalValue INT)INSERT INTO Tab1
SELECT 'P1','2008-07-01',1 UNION ALL
SELECT 'P2','2008-07-01',2 UNION ALL
SELECT 'P1','2008-08-01',5 UNION ALL
SELECT 'P2','2008-08-01',0 UNION ALL
SELECT 'P1','2008-09-01',0 UNION ALL
SELECT 'P2','2008-09-01',0 UNION ALL
SELECT 'P1','2008-10-01',6 UNION ALL
SELECT 'P2','2008-10-01',10 UNION ALL
SELECT 'P1','2008-11-01',0 UNION ALL
SELECT 'P2','2008-11-01',20
SELECT ProductID,EnterDate,
(select top 1 TotalValue from Tab1 b where a.ProductID=b.ProductID and b.TotalValue<>0
and a.EnterDate>=b.EnterDate
order by abs(datediff(d,a.EnterDate,b.EnterDate)) asc)
FROM Tab1 a
SELECT 'P1','2008-07-01',1 UNION ALL
SELECT 'P2','2008-07-01',2 UNION ALL
SELECT 'P1','2008-08-01',5 UNION ALL
SELECT 'P2','2008-08-01',0 UNION ALL
SELECT 'P1','2008-09-01',0 UNION ALL
SELECT 'P2','2008-09-01',0 UNION ALL
SELECT 'P1','2008-10-01',6 UNION ALL
SELECT 'P2','2008-10-01',10 UNION ALL
SELECT 'P1','2008-11-01',0 UNION ALL
SELECT 'P2','2008-11-01',20
SELECT ProductID, EnterDate
,TotalValue=CASE TotalValue WHEN 0 THEN (SELECT TOP 1 TotalValue
FROM TB WHERE ProductID=T.ProductID AND TotalValue>0
ORDER BY abs(Datediff(d, EnterDate, T.EnterDate))) ELSE TotalValue
END
FROM TB T
/*
ProductID EnterDate TotalValue
---------------- ----------------------- -----------
P1 2008-07-01 00:00:00.000 1
P2 2008-07-01 00:00:00.000 2
P1 2008-08-01 00:00:00.000 5
P2 2008-08-01 00:00:00.000 2
P1 2008-09-01 00:00:00.000 6
P2 2008-09-01 00:00:00.000 10
P1 2008-10-01 00:00:00.000 6
P2 2008-10-01 00:00:00.000 10
P1 2008-11-01 00:00:00.000 6
P2 2008-11-01 00:00:00.000 20(10 行受影响)
*/
drop table tb
go
create table [tb]([productid] varchar(2),[enterdate] datetime,[totalvalue] int)
insert [tb]
select 'p1','2008-7-01',1 union all
select 'p2','2008-7-01',2 union all
select 'p1','2008-8-01',5 union all
select 'p2','2008-8-01',0 union all
select 'p1','2008-9-01',0 union all
select 'p2','2008-9-01',0 union all
select 'p1','2008-10-01',6 union all
select 'p2','2008-10-01',10 union all
select 'p1','2008-11-01',0 union all
select 'p2','2008-11-01',20select productid,enterdate
,totalvalue=case totalvalue when 0 then (select top 1 totalvalue from tb
where productid=a.productid and enterdate<a.enterdate and totalvalue<>0
order by enterdate desc)
else totalvalue
end
from tb a
--测试结果:
/*
productid enterdate totalvalue
--------- ----------------------- -----------
p1 2008-07-01 00:00:00.000 1
p2 2008-07-01 00:00:00.000 2
p1 2008-08-01 00:00:00.000 5
p2 2008-08-01 00:00:00.000 2
p1 2008-09-01 00:00:00.000 5
p2 2008-09-01 00:00:00.000 2
p1 2008-10-01 00:00:00.000 6
p2 2008-10-01 00:00:00.000 10
p1 2008-11-01 00:00:00.000 6
p2 2008-11-01 00:00:00.000 20(10 行受影响)*/