select left(a.年度月份,4) as 年度, (select sum(期初余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'01')) as 年度1月份的期初余额, sum(a.本月入库额) as 年度入库额, sum(a.本月出库额) 年度出库额, (select sum(期末余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'12')) as 年度12月份的期末额 from 库存盘点 as a group by left(a.年度月份,4)
select left(a.kc_month,4) as kc_month,sum(cbje_rk) as cbje_rk,min(b.cbje_S) as cbje_S,min(c.cbje_E) as cbje_E from kc_zw a join (select kc_month,sum(cbje_S) as cbje_S from kc_zw where right(kc_month,2)='01' group by kc_month) b on left(a.kc_month,4)=left(b.kc_month,4) join (select kc_month,sum(cbje_E) as cbje_E from kc_zw where right(kc_month,2)='12' group by kc_month) c on left(a.kc_month,4)=left(c.kc_month,4) group by left(a.kc_month,4) order by left(a.kc_month,4)我这样写了,但是今年还没到12月份,想到9月末的数据,就不知道怎么办了?难道非要临时表吗?
select left(a.年度月份,4) as 年度月份,sum(本月入库额) as 本年入库额,sum(本月出库额) as 本年出库额,min(b.期初数据) as 本年期初数据,min(c.期末数据) as 本年期末数据 from 库存盘点 a join (select 年度月份,sum(期初数据) as 期初数据 from 库存盘点 where right(年度月份,2)='01' group by 年度月份) b on left(a.年度月份,4)=left(b.年度月份,4) join (select 年度月份,sum(期末数据) as 期末数据 from 库存盘点 where right(年度月份,2)='12' group by 年度月份) c on left(a.年度月份,4)=left(c.年度月份,4) group by left(a.年度月份,4) order by left(a.年度月份,4)谢谢各位,尤其是 reenjie ,完全理解了我的业务。 现在是只能统计全年的数据,如果未到一年,暂时无法统计了!
select left(a.年度月份,4) as 年度月份,sum(本月入库额) as 本年入库额, sum(本月出库额) as 本年出库额,min(b.期初数据) as 本年期初数据,min(c.期末数据) as 本年期末数据 from 库存盘点 a join (select 年度月份,sum(期初数据) as 期初数据 from 库存盘点 as t where right(年度月份,2)='01' group by 年度月份) b on left(a.年度月份,4)=left(b.年度月份,4) join (select 年度月份,sum(期末数据) as 期末数据 from 库存盘点 where right(年度月份,2)= (select MAX(right(年度月份,2) from 库存盘点 as t1 where LEFT(t1.年度月份,4)=LEFT(t2.年度月份,4) ) group by 年度月份) c on left(a.年度月份,4)=left(c.年度月份,4)
group by left(a.年度月份,4) order by left(a.年度月份,4)试试这个
select left(a.年度月份,4) as 年度月份,sum(本月入库额) as 本年入库额, sum(本月出库额) as 本年出库额,min(b.期初数据) as 本年期初数据,min(c.期末数据) as 本年期末数据 from 库存盘点 a join (select 年度月份,sum(期初数据) as 期初数据 from 库存盘点 as t where right(年度月份,2)='01' group by 年度月份) b on left(a.年度月份,4)=left(b.年度月份,4) join (select 年度月份,sum(期末数据) as 期末数据 from 库存盘点 where right(年度月份,2)= (select MAX(right(年度月份,2) from 库存盘点 as t1 where LEFT(t1.年度月份,4)=LEFT(t2.年度月份,4) ) group by 年度月份) c on left(a.年度月份,4)=left(c.年度月份,4)
group by left(a.年度月份,4) order by left(a.年度月份,4)感谢,不过有错误, MAX(right(年度月份,2)少了一个右括号,再就是依然没统计出2014年的数据,不知道是哪错了,我正在分析,你的方法很好,给了我一个思路
没有元数据不太好写,select left(a.年度月份,4) as 年度月份,sum(本月入库额) as 本年入库额, sum(本月出库额) as 本年出库额,min(b.期初数据) as 本年期初数据,min(c.期末数据) as 本年期末数据 from 库存盘点 a join (select 年度月份,sum(期初数据) as 期初数据 from 库存盘点 as t where right(年度月份,2)='01' group by 年度月份) b on left(a.年度月份,4)=left(b.年度月份,4) join (select 年度月份,sum(期末数据) as 期末数据 from 库存盘点 where right(年度月份,2)= (select MAX(right(年度月份,2) from 库存盘点 as t1 where LEFT(t1.年度月份,4)=LEFT(t.年度月份,4) ) group by 年度月份) c on left(a.年度月份,4)=left(c.年度月份,4)
group by left(a.年度月份,4) order by left(a.年度月份,4)前面貌似把别名搞错了。你试试这个
其实用个dense_rank 也可以的。(partition by left(a.年度月份,4) order by a.年度月份 desc )as 1 这样可以算出每年最后一个月份。期初余额比较金额 就是1月份的总和。期末余额就是RN=1的的总和就是期末余额。
这样啊,取当年最大的月份就OK了 select left(a.年度月份,4) as 年度, (select sum(期初余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'01') as 年度1月份的期初余额, sum(a.本月入库额) as 年度入库额 ,sum(a.本月出库额) 年度出库额, (select sum(期末余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+right('00'+cast(max(right(a.年度月份,2)) as varchar(2)),2)) as 年度12月份的期末额 from 库存盘点 as a group by left(a.年度月份,4)
select left(a.年度月份,4) as 年度,库房, (select sum(期初余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'01') as 年度1月份的期初余额, sum(a.本月入库额) as 年度入库额 ,sum(a.本月出库额) 年度出库额, (select sum(期末余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+right('00'+cast(max(right(a.年度月份,2)) as varchar(2)),2)) as 年度12月份的期末额 from 库存盘点 as a group by left(a.年度月份,4),库房这样写了以后,期初余额不正确,不能统计每个库房的期初期末数据 如果这样: select left(a.年度月份,4) as 年度,库房, (select kf,sum(期初余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'01' group by 库房) as 年度1月份的期初余额, sum(a.本月入库额) as 年度入库额 ,sum(a.本月出库额) 年度出库额, (select sum(期末余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+right('00'+cast(max(right(a.年度月份,2)) as varchar(2)),2)) as 年度12月份的期末额 from 库存盘点 as a group by left(a.年度月份,4)提示错误
(select sum(期初余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'01')) as 年度1月份的期初余额,
sum(a.本月入库额) as 年度入库额,
sum(a.本月出库额) 年度出库额,
(select sum(期末余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'12')) as 年度12月份的期末额
from 库存盘点 as a
group by left(a.年度月份,4)
select left(a.年度月份,4) as 年度月份,sum(本月入库额) as 本年入库额,sum(本月出库额) as 本年出库额,min(b.期初数据) as 本年期初数据,min(c.期末数据) as 本年期末数据
from 库存盘点 a join (select 年度月份,sum(期初数据) as 期初数据 from 库存盘点 where right(年度月份,2)='01' group by 年度月份) b on left(a.年度月份,4)=left(b.年度月份,4)
join (select 年度月份,sum(期末数据) as 期末数据 from 库存盘点 where right(年度月份,2)='12' group by 年度月份) c on left(a.年度月份,4)=left(c.年度月份,4)
group by left(a.年度月份,4) order by left(a.年度月份,4)谢谢各位,尤其是 reenjie ,完全理解了我的业务。
现在是只能统计全年的数据,如果未到一年,暂时无法统计了!
sum(本月出库额) as 本年出库额,min(b.期初数据) as 本年期初数据,min(c.期末数据) as 本年期末数据
from 库存盘点 a join (select 年度月份,sum(期初数据) as 期初数据 from 库存盘点 as t
where right(年度月份,2)='01' group by 年度月份) b on left(a.年度月份,4)=left(b.年度月份,4)
join (select 年度月份,sum(期末数据) as 期末数据
from 库存盘点 where right(年度月份,2)=
(select MAX(right(年度月份,2) from 库存盘点 as t1 where LEFT(t1.年度月份,4)=LEFT(t2.年度月份,4) )
group by 年度月份) c
on left(a.年度月份,4)=left(c.年度月份,4)
group by left(a.年度月份,4)
order by left(a.年度月份,4)试试这个
select left(a.年度月份,4) as 年度月份,sum(本月入库额) as 本年入库额,
sum(本月出库额) as 本年出库额,min(b.期初数据) as 本年期初数据,min(c.期末数据) as 本年期末数据
from 库存盘点 a join (select 年度月份,sum(期初数据) as 期初数据 from 库存盘点 as t
where right(年度月份,2)='01' group by 年度月份) b on left(a.年度月份,4)=left(b.年度月份,4)
join (select 年度月份,sum(期末数据) as 期末数据
from 库存盘点 where right(年度月份,2)=
(select MAX(right(年度月份,2) from 库存盘点 as t1 where LEFT(t1.年度月份,4)=LEFT(t2.年度月份,4) )
group by 年度月份) c
on left(a.年度月份,4)=left(c.年度月份,4)
group by left(a.年度月份,4)
order by left(a.年度月份,4)感谢,不过有错误, MAX(right(年度月份,2)少了一个右括号,再就是依然没统计出2014年的数据,不知道是哪错了,我正在分析,你的方法很好,给了我一个思路
sum(本月出库额) as 本年出库额,min(b.期初数据) as 本年期初数据,min(c.期末数据) as 本年期末数据
from 库存盘点 a join (select 年度月份,sum(期初数据) as 期初数据 from 库存盘点 as t
where right(年度月份,2)='01' group by 年度月份) b on left(a.年度月份,4)=left(b.年度月份,4)
join (select 年度月份,sum(期末数据) as 期末数据
from 库存盘点 where right(年度月份,2)=
(select MAX(right(年度月份,2) from 库存盘点 as t1 where LEFT(t1.年度月份,4)=LEFT(t.年度月份,4) )
group by 年度月份) c
on left(a.年度月份,4)=left(c.年度月份,4)
group by left(a.年度月份,4)
order by left(a.年度月份,4)前面貌似把别名搞错了。你试试这个
这样可以算出每年最后一个月份。期初余额比较金额 就是1月份的总和。期末余额就是RN=1的的总和就是期末余额。
这样啊,取当年最大的月份就OK了 select left(a.年度月份,4) as 年度,
(select sum(期初余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'01') as 年度1月份的期初余额,
sum(a.本月入库额) as 年度入库额
,sum(a.本月出库额) 年度出库额,
(select sum(期末余额) from 库存盘点
where 年度月份=cast(left(a.年度月份,4) as varchar(4))+right('00'+cast(max(right(a.年度月份,2)) as varchar(2)),2)) as 年度12月份的期末额
from 库存盘点 as a group by left(a.年度月份,4)
select left(a.年度月份,4) as 年度,库房,
(select sum(期初余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'01') as 年度1月份的期初余额,
sum(a.本月入库额) as 年度入库额
,sum(a.本月出库额) 年度出库额,
(select sum(期末余额) from 库存盘点
where 年度月份=cast(left(a.年度月份,4) as varchar(4))+right('00'+cast(max(right(a.年度月份,2)) as varchar(2)),2)) as 年度12月份的期末额
from 库存盘点 as a group by left(a.年度月份,4),库房这样写了以后,期初余额不正确,不能统计每个库房的期初期末数据
如果这样: select left(a.年度月份,4) as 年度,库房,
(select kf,sum(期初余额) from 库存盘点 where 年度月份=cast(left(a.年度月份,4) as varchar(4))+'01' group by 库房) as 年度1月份的期初余额,
sum(a.本月入库额) as 年度入库额
,sum(a.本月出库额) 年度出库额,
(select sum(期末余额) from 库存盘点
where 年度月份=cast(left(a.年度月份,4) as varchar(4))+right('00'+cast(max(right(a.年度月份,2)) as varchar(2)),2)) as 年度12月份的期末额
from 库存盘点 as a group by left(a.年度月份,4)提示错误