----创建测试数据
declare @t table(yyyymmdd varchar(10),comid varchar(10),stock int,checkin int,salesub int)
insert @t
select '20070801', '0001', 0, 20, 8 union all
select '20070802', '0001', 12, 10, 13 union all
select '20070803', '0001', 9, 30, 16 union all
select '20070801', '0002', 12, 30, 10----查询
SELECT
月份 = left(yyyymmdd,6) ,
商品 = comid,
期初库存 = (select top 1 stock from @t where comid = a.comid ORDER BY yyyymmdd),
期末库存 = (select top 1 stock from @t where comid = a.comid ORDER BY yyyymmdd) + sum(isnull(checkin,0)-isnull(salesub,0)),
进货 = sum(checkin),
销售 = sum(salesub)
FROM @t as a GROUP BY left(yyyymmdd,6),comid/*结果
月份 商品 期初库存 期末库存 进货 销售
-------------------------------------------------------
200708 0001 0 23 60 37
200708 0002 12 32 30 10
*/
declare @t table(yyyymmdd varchar(10),comid varchar(10),stock int,checkin int,salesub int)
insert @t
select '20070801', '0001', 0, 20, 8 union all
select '20070802', '0001', 12, 10, 13 union all
select '20070803', '0001', 9, 30, 16 union all
select '20070801', '0002', 12, 30, 10----查询
SELECT
月份 = left(yyyymmdd,6) ,
商品 = comid,
期初库存 = (select top 1 stock from @t where comid = a.comid ORDER BY yyyymmdd),
期末库存 = (select top 1 stock from @t where comid = a.comid ORDER BY yyyymmdd) + sum(isnull(checkin,0)-isnull(salesub,0)),
进货 = sum(checkin),
销售 = sum(salesub)
FROM @t as a GROUP BY left(yyyymmdd,6),comid/*结果
月份 商品 期初库存 期末库存 进货 销售
-------------------------------------------------------
200708 0001 0 23 60 37
200708 0002 12 32 30 10
*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货