解决方案 »
- SQL2005 求一触发器
- 任务管理器中发现“sqlservr.exe”进程占内存800多兆,什么原因?
- 如何设置sql server数据库时间?
- 怎末将dmp文件导入到sqlserver中
- 求助高手解决改变表结构问题(本人刚学sql)谢谢!
- 请帮我看看这个分布式处理的问题???
- 现在要将结果[表c](NEW) 更新另外一个表[表a]的val1, val2, val3。SQL如何写???
- 求SQL语句,也许很难,也许很简单。与除零有关!!!
- 我已将字段tab1设置为smalldatetime,同时默认值为getdate(),为何在表中体现时无秒数(sql server)
- 分页存储过程里的print @sql不知道为何打印不出来
- 求SQL语句,查询出在某个时间段内ID出现超过3次的ID。
- SQL中的数据 更新 Access 中数据 ,表结构一样
--INSERT into m(ID,mCount,mTime)
--SELECT 2,50,'2014-02-01' UNION ALL
--SELECT 2,60,'2014-02-02' UNION ALL
--SELECT 2,70,'2014-02-03' UNION ALL
--SELECT 2,80,'2014-02-04' /*
ID,进货数mCount ,进货时间mTime,已出货数
1,70,'2014-02-03' 10--这里标示部分出货。
1,80,'2014-02-04' 0
*/
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY id ORDER BY mtime)oid
FROM m),cte2 AS
(
SELECT * ,120-mcount AS [剩余数量]
FROM cte
WHERE oid=1
UNION ALL
SELECT a.id,a.mcount,a.mtime,a.oid,CASE WHEN b.[剩余数量]-a.mcount>=0 THEN b.[剩余数量]-a.mcount ELSE 0 END [剩余数量]--b.[剩余数量]
FROM cte a INNER JOIN cte2 b ON a.oid=b.oid+1 AND a.id=b.id
)
SELECT id,mcount,mtime,(SELECT [剩余数量] FROM cte2 b WHERE a.id=b.id AND a.oid-1=b.oid)[已出货数]
FROM cte2 a
WHERE mcount>(SELECT [剩余数量] FROM cte2 b WHERE a.id=b.id AND a.oid-1=b.oid)
ORDER BY id,oid
/*
id mcount mtime 已出货数
----------- ----------- ----------------------- -----------
1 70 2014-02-03 00:00:00.000 10
1 80 2014-02-04 00:00:00.000 0
2 70 2014-02-03 00:00:00.000 10
2 80 2014-02-04 00:00:00.000 0 */
select a.id,a.mcount ,a.mtime,a.tmcount,
case when a.mcount-(a.tmcount-120) <0 then 0 else a.mcount-(a.tmcount-120)end as '出货' from
(
select a.*,sum(isnull(b.mcount,0)) as Tmcount from m a left join m b on a.mtime>=b.mtime
group by a.id,a.mcount,a.mtime
)a
where a.mcount>(a.mcount-(a.tmcount-120))id mcount mtime tmcount 出货
----------- ----------- ----------------------- ----------- -----------
1 70 2014-02-03 00:00:00.000 180 10
1 80 2014-02-04 00:00:00.000 260 0(2 行受影响)