帮我看个sql 求一段时间内期末结余的
这是物品库存的 fnum是数量 fgoods_id是物品id 先求出第2个sql的sum(fnum) - 第一个sql 的sum(fnum) 第三个是我写的出错了 谁能指点下
select fgoods_id, sum(fnum)as dd from t_stock_detail group by fgoods_id
这是求查询时间段内库存的
select fgoods_id, sum(fnum) as cc from t_stock_detail where (convert(varchar,indate,120) between '2008-11-01' and '2008-11-20') group by fgoods_id
求期末结余的用cc - dd 我写了sql有错误
select t1.fgoods_id,(t2.cc-t1.dd)as ee from
((select fgoods_id, sum(fnum)as dd from t_stock_detail t1 group by fgoods_id) join
(select fgoods_id, sum(fnum) as cc from t_stock_detail t2 where (convert(varchar,indate,120) between '2008-11-01' and '2008-11-20') group by fgoods_id)
on t1.fgoods_id = t2.fgoods_id )
这是物品库存的 fnum是数量 fgoods_id是物品id 先求出第2个sql的sum(fnum) - 第一个sql 的sum(fnum) 第三个是我写的出错了 谁能指点下
select fgoods_id, sum(fnum)as dd from t_stock_detail group by fgoods_id
这是求查询时间段内库存的
select fgoods_id, sum(fnum) as cc from t_stock_detail where (convert(varchar,indate,120) between '2008-11-01' and '2008-11-20') group by fgoods_id
求期末结余的用cc - dd 我写了sql有错误
select t1.fgoods_id,(t2.cc-t1.dd)as ee from
((select fgoods_id, sum(fnum)as dd from t_stock_detail t1 group by fgoods_id) join
(select fgoods_id, sum(fnum) as cc from t_stock_detail t2 where (convert(varchar,indate,120) between '2008-11-01' and '2008-11-20') group by fgoods_id)
on t1.fgoods_id = t2.fgoods_id )
select s1.fgoods_id, s1.dd-isnull(s2.cc,0) as fnum_balance
from
(select fgoods_id, sum(fnum)as dd from t_stock_detail group by fgoods_id) s1 left join
(select fgoods_id, sum(fnum) as cc from t_stock_detail where (convert(varchar,indate,120) between '2008-11-01' and '2008-11-20') group by fgoods_id) s2
on s1.fgoods_id=s2.fgoods_id用left join,因为期间内可能没有发生数,但也要算出期末数,所以不能直接join。
另一个写法应该也能满足你的要求:
select
fgoods_id,
sum(fnum)-sum(case when convert(varchar,indate,120) between '2008-11-01' and '2008-11-20' then fnum else 0 end)
from t_stock_detail
group by fgoods_id
select A.fgoods_id,(A.cc-A.dd)as ee
from
(
(select fgoods_id, sum(fnum)as dd from t_stock_detail t1 group by fgoods_id
)
join
(select fgoods_id, sum(fnum) as cc from t_stock_detail t2
where (convert(varchar,indate,120) between '2008-11-01' and '2008-11-20')
group by fgoods_id
)
on t1.fgoods_id = t2.fgoods_id
) AS A
from
(
(select fgoods_id, sum(fnum)as dd from t_stock_detail t1 group by fgoods_id
) As T1
join
(select fgoods_id, sum(fnum) as cc from t_stock_detail t2
where (convert(varchar,indate,120) between '2008-11-01' and '2008-11-20')
group by fgoods_id
) As T2
on t1.fgoods_id = t2.fgoods_id
) AS A
from
((select fgoods_id, sum(fnum)as dd from t_stock_detail t1 group by fgoods_id) K join
(select fgoods_id, sum(fnum) as cc from t_stock_detail t2 where (convert(varchar,indate,120) between '2008-11-01' and '2008-11-20') group by fgoods_id) Ton t1.fgoods_id = t2.fgoods_id )