不好意思,有点乱了,重发一次,请高手帮忙
有两个基本表:rc(入库资料)有字段:rcrq(日期쪡),item(摘要),weigh(重量)
cc(出库资料)有字段:ccrq(日期),item(摘要),weigh(重量)
现在想组建一个这样的数据表
有字段:rq,item,rcweigh,ccweigh,weigh
按日期实现如下的功能:
rc
rcrq item weigh
2005-1-30 生产入库 1000
2005-2-4 生产入库 600
2005-2-8 生产入库 500
2005-2-10 8:00 生产入库 800 cc
ccrq item weigh
2005-1-31 销售 800
2005-2-5 销售 500
2005-2-6 销售 200
2005-2-10 10:00 销售 800如果我想统计2005年的出入库明细则形成下面的一个数据表
rq item rcweigh ccweigh weigh
上期库存 200
2005-2-4 生产入库 600 800
2005-2-5 销售 500 300
2005-2-6 销售 200 100
2005-2-8 生产入库 500 600
2005-2-10 生产入库 800 1400
2005-2-10 销售 800 600
有两个基本表:rc(入库资料)有字段:rcrq(日期쪡),item(摘要),weigh(重量)
cc(出库资料)有字段:ccrq(日期),item(摘要),weigh(重量)
现在想组建一个这样的数据表
有字段:rq,item,rcweigh,ccweigh,weigh
按日期实现如下的功能:
rc
rcrq item weigh
2005-1-30 生产入库 1000
2005-2-4 生产入库 600
2005-2-8 生产入库 500
2005-2-10 8:00 生产入库 800 cc
ccrq item weigh
2005-1-31 销售 800
2005-2-5 销售 500
2005-2-6 销售 200
2005-2-10 10:00 销售 800如果我想统计2005年的出入库明细则形成下面的一个数据表
rq item rcweigh ccweigh weigh
上期库存 200
2005-2-4 生产入库 600 800
2005-2-5 销售 500 300
2005-2-6 销售 200 100
2005-2-8 生产入库 500 600
2005-2-10 生产入库 800 1400
2005-2-10 销售 800 600
解决方案 »
- 数据库信息的查询
- VB中执行SQL语句出现超时错误,我要崩溃了,求救
- SQL Server2000在XP sp2下安装错误!(还有100分)
- endpoint nt authority anonymous logon' 登录失败
- SQL2000企业版安装在群集下安装的问题
- 急,如何在用户函数中用变量作为查询语句的表名
- 在VF中的过程和函数
- select id
- 请问如果数据量很大,比如大型企业的即时采集数据,应该用什么数据库系统处理呢?oracle,sysbase,db2,sqlserver还是cobol??
- SQL Server 对等事务复制---读写分离
- 此句错在哪里?
- 两个结果集合并问题,高手请进
insert into rc
select '2005-1-30', '生' ,1000 union all
select '2005-2-4' , '生' , 600 union all
select '2005-2-8' , '生' , 500 union all
select '2005-2-10 8:00' , '生', 800
Create Table Cc(ccrq datetime,item varchar(20),weigh int)
insert into Cc
select '2005-1-31', '售' , 800 union all
select '2005-2-5' , '售' , 500 union all
select '2005-2-6' , '售' , 200 union all
select '2005-2-10 10:00' , '售', 800
---------------------處理方----------------
create proc kkkk
as
select * into #b
from (select rcrq,item,weigh,p=1 from rc union all
select ccrq,item,weigh*-1,p=0 from Cc) a order by rcrq
------------------------------------------------------
select rq=convert(varchar(10),rcrq,120),item,rcweigh=(case when p=1 then weigh else 0 end) ,
ccweigh=(case when p=0 then weigh else 0 end),weigh=(select sum(weigh) from #b where rcrq<=a.rcrq) from #b a
-------------結束過程 ----------------
exec kkkk ----掃行過程
-------------結果-----------------
2005-01-30 生 1000 0 1000
2005-01-31 售 0 -800 200
2005-02-04 生 600 0 800
2005-02-05 售 0 -500 300
2005-02-06 售 0 -200 100
2005-02-08 生 500 0 600
2005-02-10 生 800 0 1400
2005-02-10 售 0 -800 600
drop table cc
--生成测试数据
create table rc(
rcrq datetime,
item varchar(10),
weigh int)
insert into rc select '2005-01-30 00:00:00','生产入库',1000
insert into rc select '2005-02-04 00:00:00','生产入库',600
insert into rc select '2005-02-08 00:00:00','生产入库',500
insert into rc select '2005-02-10 08:00:00','生产入库',800create table cc(
ccrq datetime,
item varchar(10),
weigh int)insert into cc select '2005-01-31 00:00:00','销售',800
insert into cc select '2005-02-05 00:00:00','销售',500
insert into cc select '2005-02-06 00:00:00','销售',200
insert into cc select '2005-02-10 10:00:00','销售',800
--执行查询
select
a.rq,
a.item,
rcweigh = (case when item = '生产入库' then weigh end),
ccweigh = (case when item = '销售' then weigh end),
weigh = (select sum(weigh) from (select rcrq as rq,weigh from rc union all select ccrq,(-1*weigh) from cc) d where d.rq <=a.rq)
from
(select rcrq as rq,item,weigh from rc
union all
select ccrq as rq,item,weigh from cc) a
where
datediff(month,a.rq,'2005-02-01')=0
union all
select
null,
'上期库存',
null,
null,
sum(weigh)
from
(select rcrq as rq,weigh from rc union all select ccrq,(-1*weigh) from cc) d
where
d.rq <='2005-02-01'
order by
rq
-------------------------------------------------
上期库存 200
2005-02-04 生产入库 600 800
2005-02-05 销售 500 300
2005-02-06 销售 200 100
2005-02-08 生产入库 500 600
2005-02-10 生产入库 800 1400
2005-02-10 销售 800 600
create proc kkkk
as
select * into #b
from (select rcrq,item,weigh,p=1 from rc union all
select ccrq,item,weigh*-1,p=0 from Cc) a order by rcrq
------------------------------------------------------
select rq=convert(varchar(10),rcrq,120),item,rcweigh=(case when p=1 then weigh else 0 end) ,
ccweigh=(case when p=0 then weigh else 0 end),weigh=(select sum(weigh) from #b where rcrq<=a.rcrq)
,bb=identity(int,1,1) into #c from #b a
-----------------
select rq=null,item='上期库存',rcweigh=0,ccweigh=0,weigh from #c a where not exists(select * from #c where bb>a.bb and datepart(yy,rq)=2005 and datepart(mm,rq)<2 )
and datepart(yy,rq)=2005 and datepart(mm,rq)<2 union all
select rq,item,rcweigh,ccweigh,weigh from #c where datepart(yy,rq)=2005 and datepart(mm,rq)>=2
-------------結束過程 ----------------
exec kkkk ----掃行過程
-------------結果-----------------
NULL 上期库存 0 0 200
2005-02-04 生 600 0 800
2005-02-05 售 0 -500 300
2005-02-06 售 0 -200 100
2005-02-08 生 500 0 600
2005-02-10 生 800 0 1400
2005-02-10 售 0 -800 600
--这样就可以入库优先,不过,这样做我得到最后两行库都是600,因为我是用sum()来简单库库
Create Table rc ( rcrq datetime, item varchar(20), weigh int)
insert into rc
select '2005-1-30', '生产入库' ,1000 union all
select '2005-2-4' , '生产入库' , 600 union all
select '2005-2-8' , '生产入库' , 500 union all
select '2005-2-10 ' , ' 产入库', 800
Create Table Cc(ccrq datetime,item varchar(20),weigh int)
insert into Cc
select '2005-1-31', '销售' , 800 union all
select '2005-2-5' , '销售' , 500 union all
select '2005-2-6' , '销售' , 200 union all
select '2005-2-10 ' , '销售', 800--方法:在item为销售的行,在时间在加上'00:00:01',使得同一日期入库时间排前,然后
--把两表信息存入一个表中
select rq=rcrq,item=item,weigh into a from rc
insert into a select ccrq+'00:00:01',item=item,-weigh from cc--构造一个函数
create function f_try(@a datetime)
returns int
asbegin
declare @b int
select @b=sum(weigh) from a where rq<=@a
return @b
end
--查询
select rq=convert(char(10),rq,120),item,rcweigh=(
case when item='生产入库' then weigh else '' end
),
ccweigh=(
case when item='销售' then -weigh else '' end
),weigh=dbo.f_try(rq) from a order by rq,item--结果
2005-01-30 生产入库 1000 0 1000
2005-01-31 销售 0 800 200
2005-02-04 生产入库 600 0 800
2005-02-05 销售 0 500 300
2005-02-06 销售 0 200 100
2005-02-08 生产入库 500 0 600
2005-02-10 生产入库 800 0 1400
2005-02-10 销售 0 800 600--疑问:
rq item rcweigh ccweigh weigh
上期库存 200
--对于这个,你是分月来算?这个月以前的都称为上期结算?
select rq='',item='上期库存',rcweigh='',ccweigh='',weight=sum(weigh) from a
where month(rq)<month(getdate())
union all
select rq=convert(char(10),rq,120),item,rcweigh=(
case when item='生产入库' then weigh else '' end
),
ccweigh=(
case when item='销售' then -weigh else '' end
),weigh=dbo.f_try(rq) from a where month(rq)=month(getdate()) order by rq
--最后把以上语句,放入存储过程中。