产品分类表 tbl_product_type
typeID typeName
产品生产表tbl_product_in
id typeID InCount(生产数量) inDate(生产的时间)产品销售表tbl_product_out
id typeID outCount(销售数量) outDate(销售的时间)
现在想要按年查询产品各个分类的生产汇总,销售汇总和剩余汇总(总剩余)
注:第一个月的剩余数量= 这个月前该分类剩余总数量+本月剩余数量
格式如下:
月份 产品类别名 生产数量 销售数量 剩余数量
2007-01 手机A 8000 5000 9000
2007-02 手机A 9000 5000 13000
2007-03 手机A 2000 0 15000
2007-04 手机A 0 0 15000
......
......2007-01 手机B 200 250 50
2007-02 手机B 300 350 0
2007-03 手机B 500 100 400
........
.......
2007-12 手机B
查询比较麻烦.分不够可再加
typeID typeName
产品生产表tbl_product_in
id typeID InCount(生产数量) inDate(生产的时间)产品销售表tbl_product_out
id typeID outCount(销售数量) outDate(销售的时间)
现在想要按年查询产品各个分类的生产汇总,销售汇总和剩余汇总(总剩余)
注:第一个月的剩余数量= 这个月前该分类剩余总数量+本月剩余数量
格式如下:
月份 产品类别名 生产数量 销售数量 剩余数量
2007-01 手机A 8000 5000 9000
2007-02 手机A 9000 5000 13000
2007-03 手机A 2000 0 15000
2007-04 手机A 0 0 15000
......
......2007-01 手机B 200 250 50
2007-02 手机B 300 350 0
2007-03 手机B 500 100 400
........
.......
2007-12 手机B
查询比较麻烦.分不够可再加
========================
快升星了 多接点分
(select isnull(i.tyepid,o.typeid) as typeid,isnull(i.indate,o.outdate) as 月份,incount as 生产数量,outcount as 销售数量,case when incount-outcount>0 then incount-outcount else 0 end as 剩余数量
from (select typeID,convert(char(7),inDate,120) as indate,sum(InCount) as InCount from tbl_product_in group by typeID,convert(char(7),inDate,120)) I
full join
(select typeID,convert(char(7),outDate,120) as outDate,sum(outCount) as outCount from tbl_product_out group by typeID,convert(char(7),outDate,120)) O
on I.typeID=O.typeID and I.inDate=o.outDate) t,tbl_product_type
where t.tyepid=tbl_product_type.typeid
tbl_product_type.typeName as '产品类别名',
isnull(tbl_product_in.Incount,0) as '生产数量',
isnull(tbl_product_out.outCount,0) as '销售数量',
isnull((select sum(InCount) from tbl_product_in aa where aa.typeID=tbl_product_in.typeID and convert(char(7),aa.inDate,120)<=tbl_product_in.inDate),0) -
isnull((select sum(outCount) from tbl_product_out bb where bb.typeID=tbl_product_out.typeID and convert(char(7),bb.outDate,120)<=tbl_product_out.outDate),0) as '剩余数量'
from (select convert(char(7),a.inDate,120) as inDate,
a.typeID,
sum(a.Incount) as Incount
from tbl_product_in a
group by convert(char(7),inDate,120),typeID)tbl_product_in
inner join tbl_product_type on tbl_product_type.typeID=tbl_product_in.typeID
left join (select convert(char(7),b.outDate,120) as outDate,
b.typeID,
sum(b.outCount) as outCount
from tbl_product_out b
group by convert(char(7),b.outDate,120),b.typeID)tbl_product_out on tbl_product_in.typeID=tbl_product_out.typeID and tbl_product_in.inDate=tbl_product_out.outDate
declare @tbl_product_type table(typeID int,typeName varchar(30))
--产品生产表
declare @tbl_product_in table(id int identity(1,1),typeID int,InCount int,inDate datetime)
--产品销售表
declare @tbl_product_out table(id int identity(1,1),typeID int,outCount int,outDate datetime)insert into @tbl_product_type
select 1,'手机A'
union select 2,'手机B'insert into @tbl_product_in(typeid,incount,indate)
select 1,8000,dateadd(m,-3,getdate())
union select 1,9000,dateadd(m,-2,getdate())
union select 1,2000,dateadd(m,-1,getdate())
union select 1,0,dateadd(m,0,getdate())
union select 2,200,dateadd(m,-3,getdate())
union select 2,300,dateadd(m,-2,getdate())
union select 2,500,dateadd(m,-1,getdate())insert into @tbl_product_out(typeid,outcount,outdate)
select 1,5000,dateadd(m,-3,getdate())
union select 1,5000,dateadd(m,-2,getdate())
union select 1,0,dateadd(m,-1,getdate())
union select 1,0,dateadd(m,-0,getdate())
union select 2,250,dateadd(m,-3,getdate())
union select 2,350,dateadd(m,-2,getdate())
union select 2,100,dateadd(m,-1,getdate())select * from @tbl_product_type
select * from @tbl_product_in order by typeid,indate
select * from @tbl_product_out order by typeid,outdateselect isnull(indate,outdate) as '月份',typename as 产品类别名,incount as '生产数量',outcount as '销售数量',ljincount-ljoutcount as 剩余数量
from
(select typeid,convert(char(7),indate,120) as indate,sum(incount) as incount,(select sum(incount) from @tbl_product_in a where a.typeid=b.typeid and a.indate<=b.indate) as ljincount
from @tbl_product_in b
group by indate,typeid
) t1 full join
(select typeid,convert(char(7),outdate,120) as outdate,sum(outcount) as outcount,(select sum(outcount) from @tbl_product_out a where a.typeid=b.typeid and a.outdate<=b.outdate) as ljoutcount
from @tbl_product_out b
group by outdate,typeid
) t2
on t1.typeid=t2.typeid and t1.indate=t2.outdate
inner join @tbl_product_type t3
on t1.typeid=t3.typeid
order by typename,月份
-----------
2007-01 手机A 8000 5000 3000
2007-02 手机A 9000 5000 7000
2007-03 手机A 2000 0 9000
2007-04 手机A 0 0 9000
2007-01 手机B 200 250 -50
2007-02 手机B 300 350 -100
2007-03 手机B 500 100 300
服务器: 消息 8624,级别 16,状态 21,行 1
内部 SQL Server 错误。如果将ljincount-ljoutcount as 剩余数量这句屏掉就正常.
错误 严重度 描述(消息文本)
8624 16 内部SQL Server 错误。