DECLARE @t TABLE([id] INT,[bookname] NVARCHAR(12),[price] DECIMAL(18,2),[net] DECIMAL(10,2)) INSERT @t SELECT 1,N'java100例',25.36,NULL UNION ALL SELECT 2,N'net100例',24.86,NULL UNION ALL SELECT 3,N'javascript详解',23.12,NULL /************/ /*Test Data*/ /***fcuandy**/ /*2009-02-02*/ /************/UPDATE a SET net = ISNULL(a.price - b.price,0) FROM @t a LEFT JOIN @t b ON a.id=b.id+1SELECT * FROM @t /* 1 java100例 25.36 0.00 2 net100例 24.86 -0.50 3 javascript详解 23.12 -1.74 */
update t set net = t.price-isnull((select top 1 price from 表 where id<t.id order by id desc),t.price) from 表 t
还要更新表中的net字段哦,要写成一个存储过程,一遍作业调用呢,这个怎么实现呢?帮帮忙?
若不连续 DECLARE @t TABLE([id] INT,[bookname] NVARCHAR(12),[price] DECIMAL(18,2),[net] DECIMAL(10,2)) INSERT @t SELECT 1,N'java100例',25.36,NULL UNION ALL SELECT 3,N'net100例',24.86,NULL UNION ALL SELECT 9,N'javascript详解',23.12,NULL /************/ /*Test Data*/ /***fcuandy**/ /*2009-02-02*/ /************/UPDATE a SET net = ISNULL(a.price - b.price,0) FROM @t a LEFT JOIN @t b ON b.id<a.id AND NOT EXISTS(SELECT 1 FROM @t WHERE id < a.id AND id>b.id)SELECT * FROM @t /* 1 java100例 25.36 0.00 3 net100例 24.86 -0.50 9 javascript详解 23.12 -1.74 */
INSERT @t SELECT 1,N'java100例',25.36,NULL
UNION ALL SELECT 2,N'net100例',24.86,NULL
UNION ALL SELECT 3,N'javascript详解',23.12,NULL
/************/
/*Test Data*/
/***fcuandy**/
/*2009-02-02*/
/************/UPDATE a SET net = ISNULL(a.price - b.price,0)
FROM @t a
LEFT JOIN @t b
ON a.id=b.id+1SELECT * FROM @t
/*
1 java100例 25.36 0.00
2 net100例 24.86 -0.50
3 javascript详解 23.12 -1.74
*/
set
net = t.price-isnull((select top 1 price from 表 where id<t.id order by id desc),t.price)
from
表 t
DECLARE @t TABLE([id] INT,[bookname] NVARCHAR(12),[price] DECIMAL(18,2),[net] DECIMAL(10,2))
INSERT @t SELECT 1,N'java100例',25.36,NULL
UNION ALL SELECT 3,N'net100例',24.86,NULL
UNION ALL SELECT 9,N'javascript详解',23.12,NULL
/************/
/*Test Data*/
/***fcuandy**/
/*2009-02-02*/
/************/UPDATE a SET net = ISNULL(a.price - b.price,0)
FROM @t a
LEFT JOIN @t b
ON b.id<a.id AND NOT EXISTS(SELECT 1 FROM @t WHERE id < a.id AND id>b.id)SELECT * FROM @t
/*
1 java100例 25.36 0.00
3 net100例 24.86 -0.50
9 javascript详解 23.12 -1.74
*/