27,579
社区成员
发帖
与我相关
我的任务
分享有出库表和入库表，求库存的问题
入库表(ruku):
classname num overtime（过期日期）
名字1 55 2009-12-12
名字1 99 2009-09-12
名字2 33 2009-11-12
名字3 66 2009-10-12
名字3 44 2009-12-00
出库表(chuku):
classname num
名字1 120
名字2 30
名字3 100
用SQL语句查出库存,过期日期排前的要先出库
classname num overtime
名字1 34 2009-12-12
名字1 0 2009-09-12
名字2 3 2009-11-12
名字3 0 2009-10-12
名字3 10 2009-12-00
classname num overtime（过期日期）
名字1 55 2009-12-12
名字1 99 2009-09-12
名字2 33 2009-11-12
名字3 66 2009-10-12
名字3 44 2009-12-00
出库表(chuku):
classname num
名字1 120
名字2 30
名字3 100
用SQL语句查出库存,过期日期排前的要先出库
classname num overtime
名字1 34 2009-12-12
名字1 0 2009-09-12
名字2 3 2009-11-12
名字3 0 2009-10-12
名字3 10 2009-12-00
...全文
请发表友善的回复…
发表回复
modie_832 2010-03-27
- 打赏
- 举报
如果加上包装规格和仓库，该怎么样的算库存？
ChinaJiaBing 2009-09-16
- 打赏
- 举报
declare @a table (classname nvarchar(10),num int,overtime datetime)
insert into @a select '名字1',55,'2009-12-12'
union all select '名字1',99,'2009-09-12'
union all select '名字2',33,'2009-11-12'
union all select '名字3',66,'2009-10-12'
union all select '名字3',44,'2009-12-1'
declare @b table (classname nvarchar(10),num int)
insert into @b select '名字1',120
union all select '名字2',30
union all select '名字3',100
; with china as
(
select classname,SUM(num)s,MAX(overtime)m from @a
group by classname
(
select classname,s=s-(select SUM(num) from @b b where a.classname=b.classname )
,m from china a
)
select isnull(a.classname,b.classname) classname,
a.s num,
isnull(a.m,b.overtime) overtime
from china1 a right join @a b on a.classname=b.classname
and a.m=b.overtime
classname num overtime
---------- ----------- -----------------------
名字1 34 2009-12-12 00:00:00.000
名字1 NULL 2009-09-12 00:00:00.000
名字2 3 2009-11-12 00:00:00.000
名字3 NULL 2009-10-12 00:00:00.000
名字3 10 2009-12-01 00:00:00.000
(5 行受影响)
gsk09 2009-09-16
- 打赏
- 举报
[Quote=引用 4 楼 gsk09 的回复:]
SQL codedeclare@rukutable ([classname]varchar(5),[num]int,[overtime]varchar(10))insertinto@rukuselect'名字1',55,'2009-12-12'unionallselect'名字1',99,'2009-09-12'unionallselect'名字2',33,'2009-11-12'unionall¡
[/Quote]
???
classname num overtime
--------- ----------- ----------
名字1 34 2009-12-12
名字1 0 2009-09-12
名字2 18 2009-11-12
名字3 0 2009-10-12
名字3 10 2009-12-00
(5 行)
SQL codedeclare@rukutable ([classname]varchar(5),[num]int,[overtime]varchar(10))insertinto@rukuselect'名字1',55,'2009-12-12'unionallselect'名字1',99,'2009-09-12'unionallselect'名字2',33,'2009-11-12'unionall¡
[/Quote]
???
declare @ruku table ([classname] varchar(5),[num] int,[overtime] varchar(10))
insert into @ruku
select '名字1',55,'2009-12-12' union all
select '名字1',99,'2009-09-12' union all
select '名字2',33,'2009-11-12' union all
select '名字3',66,'2009-10-12' union all
select '名字3',44,'2009-12-00'
--> Test data : @chuku
declare @chuku table ([classname] varchar(5),[num] int)
insert into @chuku
select '名字1',120 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字2',1 union all
select '名字3',100
--select * from @ruku
--select * from @chuku
select
classname
,((sign(num)+1)/2)*num num
,overtime
from
(select
r.classname
, (select sum(r2.num) from @ruku r2 where r.classname = r2.classname and r.[overtime]>=r2.[overtime])
-(select sum(num) from @chuku c where r.classname = c.classname ) num
,r.overtime
from @ruku r
)as t
classname num overtime
--------- ----------- ----------
名字1 34 2009-12-12
名字1 0 2009-09-12
名字2 18 2009-11-12
名字3 0 2009-10-12
名字3 10 2009-12-00
(5 行)
zs621 2009-09-15
- 打赏
- 举报
如果出库表的数据不只一条:
select a.className,a.overtime,
case when (select sum(num) from 入库表 where className=a.className and overtime<=a.overtime)<=b.num
then 0 when (select sum(num) from 入库表 where className=a.className and overtime<a.overtime)>b.num then a.num
else (select sum(num) from 入库表 where className=a.className and overtime<=a.overtime)-b.num
end
from 入库表 a left join (select sum(num) num,classname from 出库表 group by classname)b on a.className=b.classname
zs621 2009-09-15
- 打赏
- 举报
select a.className,a.overtime,
case when (select sum(num) from 入库表 where className=a.className and overtime<=a.overtime)<=b.num
then 0 when (select sum(num) from 入库表 where className=a.className and overtime<a.overtime)>b.num then a.num
else (select sum(num) from 入库表 where className=a.className and overtime<=a.overtime)-b.num
end
from 入库表 a left join 出库表 b on a.className=b.classname
如果出库表的数据不只一条 自己改吧
bing475879749 2009-09-15
- 打赏
- 举报
哎，LS几位的都有些片面，问题是我出库的记录不止一次
soft_wsx 2009-09-15
- 打赏
- 举报
/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-15 17:22:18.827●●●●●
★★★★★soft_wsx★★★★★
*/
if OBJECT_ID('入库表') is not null drop table 入库表
go
create table 入库表(classname nvarchar(20), num int, overtime nvarchar(10))
go
insert into 入库表
select
'名字1', 55, '2009-12-12' union all select
'名字1', 99, '2009-09-12' union all select
'名字2', 33, '2009-11-12' union all select
'名字3', 66, '2009-10-12' union all select
'名字3', 44, '2009-12-00'
if OBJECT_ID('出库表') is not null drop table 出库表
go
create table 出库表(classname nvarchar(20), num int)
go
insert into 出库表
select
'名字1', 120 union all select
'名字2', 30 union all select
'名字3', 100
select a.classname,num=CAse when a.num<0 then 0 else a.num end,a.overtime
from(
select a.classname,num=
(select SUM(num) from 入库表 where classname=a.classname and overtime<=a.overtime)-b.num,
a.overtime
from 入库表 a left join 出库表 b on a.classname=b.classname
)a
/*
classname num overtime
名字1 34 2009-12-12
名字1 0 2009-09-12
名字2 3 2009-11-12
名字3 0 2009-10-12
名字3 10 2009-12-00
*/
gsk09 2009-09-15
- 打赏
- 举报
classname num overtime
--------- ----------- ----------
名字1 34 2009-12-12
名字1 0 2009-09-12
名字2 3 2009-11-12
名字3 0 2009-10-12
名字3 10 2009-12-00
--------- ----------- ----------
名字1 34 2009-12-12
名字1 0 2009-09-12
名字2 3 2009-11-12
名字3 0 2009-10-12
名字3 10 2009-12-00
gsk09 2009-09-15
- 打赏
- 举报
declare @ruku table ([classname] varchar(5),[num] int,[overtime] varchar(10))
insert into @ruku
select '名字1',55,'2009-12-12' union all
select '名字1',99,'2009-09-12' union all
select '名字2',33,'2009-11-12' union all
select '名字3',66,'2009-10-12' union all
select '名字3',44,'2009-12-00'
--> Test data : @chuku
declare @chuku table ([classname] varchar(5),[num] int)
insert into @chuku
select '名字1',120 union all
select '名字2',30 union all
select '名字3',100
--select * from @ruku
--select * from @chuku
select
classname
,((sign(num)+1)/2)*num
,overtime
from
(select
r.classname
, (select sum(num) from @ruku r2 where r.classname = r2.classname and r.[overtime]>=r2.[overtime])
-(select sum(num) from @chuku c where r.classname = c.classname ) num
,r.overtime
from @ruku r
)as t
--小F-- 2009-09-15
- 打赏
- 举报
select a.classname,isnull(sum(a.num)-b.num,0)
from
ruku a
left join
chuku b
on a.classname=b.classname
group by a.classname,b.num
rucypli 2009-09-15
- 打赏
- 举报
select T1.classname,T1.m-T2.num,T1.n
from (
select classname,sum(num) as m,max(overtime) as n
from ruku
grou by classname
)T1,chuku T2
where T1.classname=T2.classnamebing475879749 2009-09-15
- 打赏
- 举报
库存是用 入库表—出库表 并排序等操作得到的
