update a set 商品数量=case when (select sum(商品数量) from tablename where 商品id ='test1' and 进货日期>=a.进货日期)<=100 then 0 else a.商品数量-(100-(select sum(商品数量) from tablename where 商品id ='test1' and 进货日期>a.进货日期)) end from tablename a where 商品id ='test1' and (select sum(商品数量) from tablename where 商品id ='test1' and 进货日期>a.进货日期)<100--未测试
建立一个函数getlastdate(),参数为@商品ID,@date_begin,@date_end,@date_begin可以取这个库表的建立时间,,@date_end可以取当前时间,也可以是其它的时间。其SQL语句为 Select TOP 1 @result_date = 进货时间 from 表名 where 商品数量>0 and 进货时间 between @date_begin and @date_end order by 进货时间 desc
建立一个函数getlastdate(),参数为@商品ID,@date_begin,@date_end,@date_begin可以取这个库表的建立时间,,@date_end可以取当前时间,也可以是其它的时间。其SQL语句为 Select TOP 1 @result_date = 进货时间 from 表名 where 商品数量>0 and 商品id=@商品ID and 进货时间 between @date_begin and @date_end
order by 进货时间 desc
DECLARE @tb TABLE([商品id] varchar(10), [商品数量] int, [进货日期] datetime) INSERT INTO @tb SELECT 'test1', 50, '2006-1-1' UNION ALL SELECT 'test1', 30, '2006-1-2' UNION ALL SELECT 'test1', 70, '2006-1-3' UNION ALL SELECT 'test2', 60, '2006-1-4' UNION ALL SELECT 'test2', 60, '2006-1-4' UNION ALL SELECT 'test2', 60, '2006-1-5'UPDATE @tb SET 商品数量 =CASE WHEN (SELECT SUM(商品数量) FROM @tb WHERE 进货日期 >= A.进货日期 AND 商品id = A.商品id) < 100 THEN 0 ELSE (SELECT SUM(商品数量) FROM @tb WHERE 进货日期 >= A.进货日期 AND 商品id = A.商品id) - 100 END FROM @tb A WHERE 商品id = 'test1'SELECT * FROM @tb商品id 商品数量 进货日期 ---------- ----------- --------------------------- test1 50 2006-01-01 00:00:00.000 test1 0 2006-01-02 00:00:00.000 test1 0 2006-01-03 00:00:00.000 test2 60 2006-01-04 00:00:00.000 test2 60 2006-01-04 00:00:00.000 test2 60 2006-01-05 00:00:00.000
set 商品数量=case when (select sum(商品数量) from tablename where 商品id ='test1' and 进货日期>=a.进货日期)<=100 then 0 else a.商品数量-(100-(select sum(商品数量) from tablename where 商品id ='test1' and 进货日期>a.进货日期)) end
from tablename a
where 商品id ='test1'
and (select sum(商品数量) from tablename where 商品id ='test1' and 进货日期>a.进货日期)<100--未测试
Select TOP 1 @result_date = 进货时间
from 表名
where 商品数量>0
and 进货时间 between @date_begin and @date_end
order by 进货时间 desc
Select TOP 1 @result_date = 进货时间
from 表名
where 商品数量>0
and 商品id=@商品ID
and 进货时间 between @date_begin and @date_end
order by 进货时间 desc
INSERT INTO @tb
SELECT 'test1', 50, '2006-1-1'
UNION ALL SELECT 'test1', 30, '2006-1-2'
UNION ALL SELECT 'test1', 70, '2006-1-3'
UNION ALL SELECT 'test2', 60, '2006-1-4'
UNION ALL SELECT 'test2', 60, '2006-1-4'
UNION ALL SELECT 'test2', 60, '2006-1-5'UPDATE @tb
SET 商品数量 =CASE WHEN (SELECT SUM(商品数量) FROM @tb WHERE 进货日期 >= A.进货日期 AND 商品id = A.商品id) < 100 THEN 0
ELSE (SELECT SUM(商品数量) FROM @tb WHERE 进货日期 >= A.进货日期 AND 商品id = A.商品id) - 100 END
FROM @tb A
WHERE 商品id = 'test1'SELECT * FROM @tb商品id 商品数量 进货日期
---------- ----------- ---------------------------
test1 50 2006-01-01 00:00:00.000
test1 0 2006-01-02 00:00:00.000
test1 0 2006-01-03 00:00:00.000
test2 60 2006-01-04 00:00:00.000
test2 60 2006-01-04 00:00:00.000
test2 60 2006-01-05 00:00:00.000