select 产品, 期初余=isnull((select top 1 上日余 from t where 产品=a.产品 and 日期 between '2005-6-1' and '2005-6-2' order by 日期),0), 期间进=isnull((select sum(本日进) from t where 产品=a.产品 and 日期 between '2005-6-1' and '2005-6-2'),0), 期间出=isnull((select sum(本日出) from t where 产品=a.产品 and 日期 between '2005-6-1' and '2005-6-2'),0), 期末余=isnull((select top 1 本日余 from t where 产品=a.产品 and 日期 between '2005-6-1' and '2005-6-2' order by 日期 desc),0) from t a where 日期 between '2005-6-1' and '2005-6-2' group by 产品
select 产品, 期间余=(select 上日余 from tab where id=min_id), 期间进,期间出, 期末余=(select 本日余 from tab where id=max_id) from ( select 产品, 期间进=sum(本日进), 期间出=sum(本日出), min_id=min(id), max_id=max(id) from tab where 日期 between '2005-6-1' and '2005-6-2' group by 产品) a
期初余=isnull((select top 1 上日余 from t where 产品=a.产品 and
日期 between '2005-6-1' and '2005-6-2' order by 日期),0),
期间进=isnull((select sum(本日进) from t where 产品=a.产品 and
日期 between '2005-6-1' and '2005-6-2'),0),
期间出=isnull((select sum(本日出) from t where 产品=a.产品 and
日期 between '2005-6-1' and '2005-6-2'),0),
期末余=isnull((select top 1 本日余 from t where 产品=a.产品 and
日期 between '2005-6-1' and '2005-6-2' order by 日期 desc),0)
from t a
where 日期 between '2005-6-1' and '2005-6-2'
group by 产品
期间余=(select 上日余 from tab where id=min_id),
期间进,期间出,
期末余=(select 本日余 from tab where id=max_id)
from (
select 产品,
期间进=sum(本日进),
期间出=sum(本日出),
min_id=min(id),
max_id=max(id)
from tab where 日期 between '2005-6-1' and '2005-6-2'
group by 产品) a