规则:1.先入库的货物要先出库
数据库:#入库表 、 #领用表
---创建表--创建 入库表
create table #入库表
(DATE datetime, --日期
ITEM varchar(10), --科目
TYPE varchar(10), --类型
QTY int) --数量
insert into #入库表 values('2011-1-1', '项目1', '入库', 10)
insert into #入库表 values('2011-1-2', '项目1', '入库', 10)
insert into #入库表 values('2011-1-3', '项目1', '入库', 20)
insert into #入库表 values('2011-1-4', '项目1', '入库', 15)
insert into #入库表 values('2011-1-5', '项目1', '入库', 5 )
insert into #入库表 values('2011-1-6', '项目1', '入库', 1 )
insert into #入库表 values('2011-1-7', '项目1', '入库', 30)--创建 出库表
create table #领用表
(DATE datetime, --日期
ITEM varchar(10), --科目
TYPE varchar(10), --类型
QTY int) --数量
insert into #领用表 values('2011-1-7', '项目1', '领用', 30 )最终的显示信息为:
DATE ITEM TYPE QTY
2011-01-03 00:00:00.000 项目1 入库 10
2011-01-04 00:00:00.000 项目1 入库 15
2011-01-05 00:00:00.000 项目1 入库 5
2011-01-06 00:00:00.000 项目1 入库 1
2011-01-07 00:00:00.000 项目1 入库 30
数据库:#入库表 、 #领用表
---创建表--创建 入库表
create table #入库表
(DATE datetime, --日期
ITEM varchar(10), --科目
TYPE varchar(10), --类型
QTY int) --数量
insert into #入库表 values('2011-1-1', '项目1', '入库', 10)
insert into #入库表 values('2011-1-2', '项目1', '入库', 10)
insert into #入库表 values('2011-1-3', '项目1', '入库', 20)
insert into #入库表 values('2011-1-4', '项目1', '入库', 15)
insert into #入库表 values('2011-1-5', '项目1', '入库', 5 )
insert into #入库表 values('2011-1-6', '项目1', '入库', 1 )
insert into #入库表 values('2011-1-7', '项目1', '入库', 30)--创建 出库表
create table #领用表
(DATE datetime, --日期
ITEM varchar(10), --科目
TYPE varchar(10), --类型
QTY int) --数量
insert into #领用表 values('2011-1-7', '项目1', '领用', 30 )最终的显示信息为:
DATE ITEM TYPE QTY
2011-01-03 00:00:00.000 项目1 入库 10
2011-01-04 00:00:00.000 项目1 入库 15
2011-01-05 00:00:00.000 项目1 入库 5
2011-01-06 00:00:00.000 项目1 入库 1
2011-01-07 00:00:00.000 项目1 入库 30
解决方案 »
- 这样一条用子查询做的SQL语句如何改为用连接的形式写出来?
- 一个数据库同步问题
- 效率?效率,还是效率的问题。。。。使用游标?
- 【求助】求temp表中SNo[数值型]列中所有不在1-1000范围内的数..
- 98系统下SQL Server2000出故障后重装SQL Server2000,但一启动服务管理器启动后立即退出?
- 从一个数据表中读取某一字段的所有数据,譬如说共有3个数据,我现在想做的是针对于这8个数据中的每一行数据,我都用一个别名来表示,例如select ss.top1 as A,ss.top2 as B,ss.top3 as C from table;其中ss为
- 奇怪的问题
- 请教一SQL语句。
- 如何在SQL-SERVER中实现数据集的交集和差集(在线给分)
- 数据库新手,求问
- 如何在Microsoft SQL Server Management Studio新建SQL查询时自动加上自定义头部注释
- sql改错,这两题都是错的。求正确答案。
http://topic.csdn.net/u/20070721/13/744BD499-A022-4CC6-AEFC-4487AB34CF94.html
create table paylog (
gold int not null,
paydate datetime not null,
des nvarchar(10) not null
)
insert into paylog
select '80','2010-09-10 13:18','付费'
union all
select '100','2010-09-11 14:18','赠送'
union all
select '30','2010-09-12 14:20','赠送'
union all
select '40','2010-09-13 14:20','付费'
union all
select '90','2010-09-14 14:20','付费'
union all
select '30','2010-09-15 14:20','赠送'
gocreate table costlog (
gold int not null,
costdate datetime not null
)
insert into costlog
select '50','2010-09-12 14:00'
union all
select '80','2010-09-13 14:18'
union all
select '10','2010-09-14 14:20'
union all
select '60','2010-09-15 14:20'
union all
select '50','2010-09-16 14:20'
union all
select '1000','2010-09-17 14:20'
--结果
/*
gold paydate des gold costdate details
80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50
80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30
100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50
100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10
100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40
30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20
30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10
40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40
90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90
30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30
*/
--1:declare @i int
set @i=1
while @i<=2000
begininsert into num
select @iset @i=@i+1
end
go;with pay_t as (select row_number() over (order by paydate) as indexno,a.*
from paylog a,num b where a.gold>=b.indexno
),cost_t as (
select row_number() over (order by costdate) as indexno,a.*
from costlog a,num b where a.gold>=b.indexno
)select a.gold,a.paydate,a.des,b.gold,b.costdate,count(1) as details
from pay_t a,cost_t b where a.indexno=b.indexno group by a.gold,a.paydate,a.des,b.gold,b.costdate
order by b.costdate,a.paydate
--2:with t1 as
(
select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from paylog where paydate <= a.paydate) from paylog a
)
,t2 as
(
select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from costlog where costdate <= a.costdate) from costlog a
)
,t3 as
(
select a.*,bid1=b.id
from t1 a
cross apply (select top (1) * from t2 where sumgold >= a.sumgold order by sumgold) b
)
,t4 as
(
select a.*,bid2=isnull(b.bid1,1) from t3 a left join t3 b on a.id = b.id + 1
)
,t5 as
(
select paygold=a.gold,a.paydate,a.des,costgold=b.gold,b.costdate,details=
case
when a.sumgold>=b.sumgold then
case when a.gold-a.sumgold+b.sumgold >= b.gold then b.gold else a.gold-a.sumgold+b.sumgold end
else
case when b.gold-b.sumgold+a.sumgold >= a.gold then a.gold else b.gold-b.sumgold+a.sumgold end
end
from t4 a join t2 b on b.id between a.bid2 and a.bid1
)
select * from t5 where details > 0
--3:create table cun(gold int,paydate datetime,[des] nvarchar(10),go int,costdate datetime,details int)
godeclare my_cursor cursor scroll
for
select * from paylog
open my_cursor
declare @gold int
declare @paydate datetime
declare @des nvarchar(10)
set @gold = 0
fetch next from my_cursor into @gold,@paydate,@des
while (@@fetch_status = 0)
begin
declare next_cursor cursor scroll
for
select * from costlog
open next_cursor
declare @go int
declare @costdate datetime
declare @details int
declare @gocun int
declare @decun int
set @decun = 0
set @go = 0
set @details = 0
set @gocun = @gold
fetch next from next_cursor into @go,@costdate
set @gocun = @gocun - @go
while(@@fetch_status = 0)
begin
if(@gocun = 0)
begin
set @details = @gold
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from my_cursor into @gold,@paydate,@des
fetch next from next_cursor into @go,@costdate
set @gocun = @gold - @go
set @decun = 0
end
if(@gocun > 0)
begin
set @details = @go - @decun
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from next_cursor into @go,@costdate
set @gocun = @gocun - @go
set @decun = 0
end
if(@gocun < 0)
begin
set @details = @go + @gocun - @decun
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from my_cursor into @gold,@paydate,@des
set @gocun = @gold + @gocun
set @decun = @details
end
end
close next_cursor
deallocate next_cursor
end
close my_cursor
deallocate my_cursor
select * from cun
id int identity(1,1),
name varchar(50),--商品名称
j int, --入库数量
c int, --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01'
gocreate proc wsp
@name varchar(50),--商品名称
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set c=
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
then a.j
else
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0
else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c)
end
end
from t a where name=@name and j!=c
end
else
raiserror('库存不足',16,1)
return
go
--测试:exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp
(DATE datetime, --日期
ITEM varchar(10), --科目
TYPE varchar(10), --类型
QTY int) --数量
insert into #入库表 values('2011-1-1', '项目1', '入库', 10)
insert into #入库表 values('2011-1-2', '项目1', '入库', 10)
insert into #入库表 values('2011-1-3', '项目1', '入库', 20)
insert into #入库表 values('2011-1-4', '项目1', '入库', 15)
insert into #入库表 values('2011-1-5', '项目1', '入库', 5 )
insert into #入库表 values('2011-1-6', '项目1', '入库', 1 )
insert into #入库表 values('2011-1-7', '项目1', '入库', 30)--创建 出库表
create table #领用表
(DATE datetime, --日期
ITEM varchar(10), --科目
TYPE varchar(10), --类型
QTY int) --数量
insert into #领用表 values('2011-1-7', '项目1', '领用', 30 )select
a.[DATE],a.ITEM,TYPE,QTY=case when a.TotalQty-a.QTY>b.TotalQty then a.QTY else a.TotalQty-b.TotalQty end
from
(select *,(select SUM(QTY) from #入库表 where ITEM=a.ITEM and DATE<=a.DATE) as TotalQty from #入库表 as a )a
left join (select ITEM,SUM(QTY) as TotalQty from #领用表 group by ITEM) as b on a.Item=b.Item
where a.TotalQty>isnull(b.TotalQty,0)
/*
DATE ITEM TYPE QTY
2011-01-03 00:00:00.000 项目1 入库 10
2011-01-04 00:00:00.000 项目1 入库 15
2011-01-05 00:00:00.000 项目1 入库 5
2011-01-06 00:00:00.000 项目1 入库 1
2011-01-07 00:00:00.000 项目1 入库 30
*a/