想生成一张详细的库存明细表，自己感觉比较复杂，大家帮忙看看

Question

我有如下两张表，
进货表
id    name     inrefno          indate            quantity
1     cpu       in2008001       2008-07-01           50
2     memory    in2008002       2008-07-05           90
3     mouse     in2008003       2008-07-03           30
出货表
id    name       outrefno            inrefno          outdate            quantity
1     memory     out2008001         in2008002         2008-07-06          10
2     cpu        out2008002         in2008001         2008-07-08          5
3     memory     out2008003         in2008002         2008-07-10          3
我想根据上面的两张表，生成一张库存数量，库放天数的明细表如下：(存放天数等于出货日期减去当月的第一天)
inrefno     name   startDate(起算日期)   startQuantity(开始数量)   outdate   outQuantity  saveDate(存放天数)  endQuantity(剩余数量)
in2008001   cpu     2008年7月1日                  50               2008-07-08    5           7                 43
in2008001   cpu     2008年7月1日                  43                                         31                43
in2008002   memory  2008年7月1日                90                2008-07-06    10         5                 85
in2008002   memory  2008年7月1日                  85                2008-07-10     3         10                82
in2008002   memory  2008年7月1日                  83                                         31                82   
in2008003   mouse   2008年7月1日                  30                                         31                30   

Answer 1

上面的代码发到论坛后很乱，重新整理一下，方便看
inrefno    name  startDate(起算日期)  startQuantity(开始数量)   outdate  outQuantity  saveDate(存放天数)  endQuantity(剩余量) 
in2008001  cpu    2008年7月1日            50                     2008-07-08    5          7                 43 
in2008001  cpu    2008年7月1日            43                                              31                43 
in2008002  memory  2008年7月1日           90                     2008-07-06    10         5                 85 
in2008002  memory  2008年7月1日           85                     2008-07-10     3         10                82 
in2008002  memory  2008年7月1日           83                                              31                82  
in2008003  mouse  2008年7月1日             30                                             31               30  

Answer 2

不是，是我看错了，应该是50-5＝45.
各位大侠有什么解决的思路吗？谢谢啊。
inrefno    name  startDate(起算日期)  startQuantity(开始数量)   outdate  outQuantity  saveDate(存放天数)  endQuantity(剩余量) 
in2008001  cpu    2008年7月1日            50                     2008-07-08    5          7                 45 
in2008001  cpu    2008年7月1日            45                                              31                45 
in2008002  memory  2008年7月1日           90                     2008-07-06    10         5                 85 
in2008002  memory  2008年7月1日           85                     2008-07-10     3         10                82 
in2008002  memory  2008年7月1日           83                                              31                82  
in2008003  mouse  2008年7月1日             30                                             31               30  

Answer 3

呵呵，有点乱
declare @in table (id int,name varchar(10),inrefno varchar(10),indate char(10),quantity int)
insert into @in 
select 1,    'cpu',      'in2008001',      '2008-07-01',          50 union
select 2,    'memory',    'in2008002',      '2008-07-05',          90 union
select 3,    'mouse',    'in2008003',      '2008-07-03',          30 declare @out table (id int,name varchar(10),outrefno varchar(10),inrefno varchar(10),outdate char(10),quantity int)
insert into @out
select 1,    'memory',    'out2008001',        'in2008002',        '2008-07-06',          10  union
select 2,    'cpu',        'out2008002' ,       'in2008001',        '2008-07-08',          5 union
select 3,    'memory',    'out2008003' ,       'in2008002',        '2008-07-10',          3 declare @ym char(7)
set @ym = '2008-07'    -- 查询月份select a.inrefno,a.name,@ym+'-01' as startdate,a.quantity-b.sum_q as startQuantity,b.outdate,b.quantity as outquantity,
         datediff(day,@ym+'-01',b.outdate) as savedate,a.quantity-b.sum_q-b.quantity as endquantity
from @in a
inner join (select a1.*,isnull(sum(b1.quantity),0) as sum_q
                from @out a1
                left join @out b1 on a1.name=b1.name and a1.inrefno=b1.inrefno and a1.outrefno<>b1.outrefno 
                                       and a1.outdate>b1.outdate and left(a1.outdate,7)=left(b1.outdate,7)
                where left(a1.outdate,7)=@ym
                group by a1.id,a1.name,a1.outrefno,a1.inrefno,a1.outdate,a1.quantity
               ) b on a.name=b.name and a.inrefno=b.inrefno
where left(a.indate,7)=@ym
union all
select a.inrefno,a.name,@ym+'-01',sum(a.quantity-isnull(b.quantity,0)),null,null,
         datediff(day,@ym+'-01',dateadd(month,1,@ym+'-01')),sum(a.quantity-isnull(b.quantity,0))
from @in a
left join (select inrefno, name,sum(quantity) as quantity from @out where left(outdate,7)=@ym group by inrefno,name
            ) b on a.name=b.name and a.inrefno=b.inrefno
where left(a.indate,7)=@ym
group by a.inrefno,a.name,a.indate
order by name,inrefno,startquantity desc/*
inrefno    name       startdate  startQuantity outdate    outquantity savedate    endquantity
---------- ---------- ---------- ------------- ---------- ----------- ----------- -----------
in2008001  cpu        2008-07-01 50            2008-07-08 5           7           45
in2008001  cpu        2008-07-01 45            NULL       NULL        31          45
in2008002  memory     2008-07-01 90            2008-07-06 10          5           80
in2008002  memory     2008-07-01 80            2008-07-10 3           9           77
in2008002  memory     2008-07-01 77            NULL       NULL        31          77
in2008003  mouse      2008-07-01 30            NULL       NULL        31          30
*/

Answer 4

create table InQ(id VARCHAR(1),name1 VARCHAR(20),inrefno VARCHAR(20),indate VARCHAR(20),quantity INT)
INSERT INTO InQ values('1','cpu','in2008001','2008-07-01',50)
INSERT INTO InQ values('2','memory','in2008002','2008-07-05',90)
INSERT INTO InQ values('3','mouse','in2008003','2008-07-03',30)create table OutQ(id VARCHAR(1),name1 VARCHAR(20),outrefno VARCHAR(20),inrefno VARCHAR(20),outdate  VARCHAR(20),quantity INT)
INSERT INTO OutQ values('1','memory','out2008001','in2008002','2008-07-06',10)
INSERT INTO OutQ values('2','cpu','out2008002','in2008001','2008-07-08',5)
INSERT INTO OutQ values('3','memory','out2008003','in2008002','2008-07-10',3) select * from 
(
SELECT a.inrefno,a.name1,a.indate,starquantity=a.quantity-isnull((select sum(quantity) from OutQ where a.inrefno=inrefno and outdate<b.outdate),0),outdate,b.quantity,Datediff(d,indate,outdate) savedate,a.quantity-(select sum(quantity) from OutQ where a.inrefno=inrefno and outdate<=b.outdate) as endquantity
FROM InQ a 
FULL OUTER JOIN OutQ b ON a.name1= b.name1
UNION ALL
SELECT a.inrefno,name1,a.indate,a.quantity-ISNULL((select sum(quantity) from OutQ where a.inrefno=inrefno),0),'',0,0,a.quantity-(select sum(quantity) from OutQ where a.inrefno=inrefno)
FROM InQ a WHERE EXISTS(SELECT 1 FROM OutQ WHERE a.name1= name1 and a.inrefno=inrefno)
) c
order by endquantity desc,outdate desc

Answer 5

in2008002 memory 2008-07-05 90 2008-07-06 10 1 80
in2008002 memory 2008-07-05 80 2008-07-10 3 5 77
in2008002 memory 2008-07-05 77 0 0 77
in2008001 cpu 2008-07-01 50 2008-07-08 5 7 45
in2008001 cpu 2008-07-01 45 0 0 45
in2008003 mouse 2008-07-03 30 NULL NULL NULL NULL

Answer 6

create table InQ(id VARCHAR(1),name1 VARCHAR(20),inrefno VARCHAR(20),indate VARCHAR(20),quantity INT)
INSERT INTO InQ values('1','cpu','in2008001','2008-07-01',50)
INSERT INTO InQ values('2','memory','in2008002','2008-07-05',90)
INSERT INTO InQ values('3','mouse','in2008003','2008-07-03',30)create table OutQ(id VARCHAR(1),name1 VARCHAR(20),outrefno VARCHAR(20),inrefno VARCHAR(20),outdate  VARCHAR(20),quantity INT)
INSERT INTO OutQ values('1','memory','out2008001','in2008002','2008-07-06',10)
INSERT INTO OutQ values('2','cpu','out2008002','in2008001','2008-07-08',5)
INSERT INTO OutQ values('3','memory','out2008003','in2008002','2008-07-10',3) select * from 
(
SELECT a.inrefno,a.name1,a.indate,starquantity=a.quantity-isnull((select sum(quantity) from OutQ where a.inrefno=inrefno and outdate<b.outdate),0),outdate,b.quantity,Datediff(d,indate,outdate) savedate,a.quantity-(select sum(quantity) from OutQ where a.inrefno=inrefno and outdate<=b.outdate) as endquantity
FROM InQ a 
FULL OUTER JOIN OutQ b ON a.name1= b.name1
UNION ALL
SELECT a.inrefno,name1,a.indate,a.quantity-ISNULL((select sum(quantity) from OutQ where a.inrefno=inrefno),0),'',0,0,a.quantity-(select sum(quantity) from OutQ where a.inrefno=inrefno)
FROM InQ a WHERE EXISTS(SELECT 1 FROM OutQ WHERE a.name1= name1 and a.inrefno=inrefno)
) c
order by endquantity desc,outdate desc

Answer 7

谢谢sdxiong了，你的代码在我这执行提示服务器: 消息 209，级别 16，状态 1，行 4
列名 'name' 不明确。
服务器: 消息 209，级别 16，状态 1，行 4
列名 'inrefno' 不明确。我这里是sql server2000

Answer 8

谢谢mugua604 了，你给出的算法好像没有算那些没有出货的单子的存放天数及最终的数量哦，我想实现的是算没有出货，也算出它的存放天数，再次谢谢了。

Answer 9

select * from 
(
SELECT a.inrefno,a.name1,a.indate,starquantity=a.quantity-isnull((select sum(quantity) from OutQ where a.inrefno=inrefno and outdate<b.outdate),0),outdate,b.quantity,Datediff(d,indate,outdate) savedate,a.quantity-isnull((select sum(quantity) from OutQ where a.inrefno=inrefno and outdate<=b.outdate) ,0)as endquantity
FROM InQ a 
FULL OUTER JOIN OutQ b ON a.name1= b.name1
UNION ALL
SELECT a.inrefno,name1,a.indate,a.quantity-ISNULL((select sum(quantity) from OutQ where a.inrefno=inrefno),0),'',0,0,a.quantity-(select sum(quantity) from OutQ where a.inrefno=inrefno)
FROM InQ a WHERE EXISTS(SELECT 1 FROM OutQ WHERE a.name1= name1 and a.inrefno=inrefno)
) c
order by endquantity desc,outdate desc
==============
inrefno              name1                indate               starquantity outdate              quantity    savedate    endquantity
-------------------- -------------------- -------------------- ------------ -------------------- ----------- ----------- -----------
in2008002            memory               2008-07-05           90           2008-07-06           10          1           80
in2008002            memory               2008-07-05           80           2008-07-10           3           5           77
in2008002            memory               2008-07-05           77                                0           0           77
in2008001            cpu                  2008-07-01           50           2008-07-08           5           7           45
in2008001            cpu                  2008-07-01           45                                0           0           45
in2008003            mouse                2008-07-03           30           NULL                 NULL        NULL        30(6 行受影响)

Answer 10

为什么我的不能用SQL CODE？ 是不是浏览器的问题呢？？

Answer 11

create table InQ(id VARCHAR(1),name1 VARCHAR(20),inrefno VARCHAR(20),indate VARCHAR(20),quantity INT)
INSERT INTO InQ values('1','cpu','in2008001','2008-07-01',50)
INSERT INTO InQ values('2','memory','in2008002','2008-07-05',90)
INSERT INTO InQ values('3','mouse','in2008003','2008-07-03',30)create table OutQ(id VARCHAR(1),name1 VARCHAR(20),outrefno VARCHAR(20),inrefno VARCHAR(20),outdate  VARCHAR(20),quantity INT)
INSERT INTO OutQ values('1','memory','out2008001','in2008002','2008-07-06',10)
INSERT INTO OutQ values('2','cpu','out2008002','in2008001','2008-07-08',5)
INSERT INTO OutQ values('3','memory','out2008003','in2008002','2008-07-10',3) select * from 
(
SELECT a.inrefno,a.name1,a.indate,starquantity=a.quantity-isnull((select sum(quantity) from OutQ where a.inrefno=inrefno and outdate<b.outdate),0),outdate,b.quantity,isnull(Datediff(d,indate,outdate),Datediff(d,indate,dateadd(dd,-1,convert(char(7),dateadd(M,1,indate),120)+'-01'))) savedate,a.quantity-isnull((select sum(quantity) from OutQ where a.inrefno=inrefno and outdate<=b.outdate) ,0)as endquantity
FROM InQ a 
FULL OUTER JOIN OutQ b ON a.name1= b.name1
UNION ALL
SELECT a.inrefno,name1,a.indate,a.quantity-ISNULL((select sum(quantity) from OutQ where a.inrefno=inrefno),0),'',0,Datediff(d,indate,dateadd(dd,-1,convert(char(7),dateadd(M,1,indate),120)+'-01')),a.quantity-(select sum(quantity) from OutQ where a.inrefno=inrefno)
FROM InQ a WHERE EXISTS(SELECT 1 FROM OutQ WHERE a.name1= name1 and a.inrefno=inrefno)
) c
order by endquantity desc,outdate desc
===================
/*
in2008002 memory 2008-07-05 90 2008-07-06 10 1 80
in2008002 memory 2008-07-05 80 2008-07-10 3 5 77
in2008002 memory 2008-07-05 77 0 26 77
in2008001 cpu 2008-07-01 50 2008-07-08 5 7 45
in2008001 cpu 2008-07-01 45 0 30 45
in2008003 mouse 2008-07-03 30 NULL NULL 28 30
*/

Answer 12

怎么还是这个样子啊!!====
inrefno              name1                indate               starquantity outdate              quantity    savedate    endquantity
-------------------- -------------------- -------------------- ------------ -------------------- ----------- ----------- -----------
in2008002            memory               2008-07-05           90           2008-07-06           10          1           80
in2008002            memory               2008-07-05           80           2008-07-10           3           5           77
in2008002            memory               2008-07-05           77                                0           26          77
in2008001            cpu                  2008-07-01           50           2008-07-08           5           7           45
in2008001            cpu                  2008-07-01           45                                0           30          45
in2008003            mouse                2008-07-03           30           NULL                 NULL        28          30

Answer 13

testselect * from tablename

Answer 14

上面这个还有些问题，如果indate,outdate是日期型,如果没有出货数据的话最后的结果outdate会出现1900-01-01这种不正确的日期

Answer 15

可以转换的啊~~
convart() 函数转换啊！