流水帐问题

Question

create table a
(
inno char(13),--出入库单号  出库以o打开,入库以i开头
indate datetime--出入库时间
)
insert into a values('i1','2010-9-10')
insert into a values('i2','2010-9-11')
insert into a values('o1','2010-9-11')
insert into a values('o2','2010-9-12')
insert into a values('i3','2010-9-13')
insert into a values('o3','2010-9-14')
create table b
(
inno char(13),--出入库单号
pname char(13),--物料编号
qty numeric(18,3)--数量

)
insert into b values('i1','aaa','50')
insert into b values('i2','aaa','50')
insert into b values('i2','bbb','50')
insert into b values('o1','aaa','10')
insert into b values('o2','aaa','20')
insert into b values('i3','aaa','60')
insert into b values('o3','bbb','60')
输入 b.pname='aaa' 时  出现如下面结果
单号 出入库时间  出库数量  入库数量  结存
i1    2010-9-10               50      50
i2    2010-9-11               50      100
o1    2010-9-11   10                   90
o2    2010-9-12   20    70
i3    2010-9-13   60                   130

Answer 1

表设计可以修改一下，SELECT a.inno,a.indate,
CASE when LEFT(a.inno,1)='o' THEN qty ELSE 0 END 出库数量,
CASE when LEFT(a.inno,1)='i' THEN qty ELSE 0 END 入库数量,
CASE when LEFT(a.inno,1)='i' THEN qty ELSE 0 END 
-CASE when LEFT(a.inno,1)='o' THEN qty ELSE 0 END 结存
FROM a
INNER JOIN b ON a.inno=b.inno
WHERE b.pname='aaa'

Answer 2

qty直接放入a表中，出入库也不同i，o区分，出为负，入为正即可a表：出入库单号，物料编号，日期，数量；只要这列即可，删除b表。

Answer 3

;with cte
as
(
select  distinct inno 单号,indate 出入库时间  ,
出库数量=(select  isnull(SUM(QTY),0) FROM #B b where b.inno=m.inno and left(inno,1)='o' and b.pname=m.pname), 
入库数量=(select  isnull(SUM(QTY),0) FROM #B b where b.inno=m.inno and left(inno,1)='i' and b.pname=m.pname) 
--结存=
,m.row
from
(    
SELECT a.inno,a.indate,b.qty,b.pname,row_number()over(order by getdate())row
FROM #A a
left join #B b
ON a.inno=b.inno
)M
)
select  单号, 出入库时间,出库数量,入库数量
,结存=(select sum(入库数量-出库数量) from cte where row<=c.row)
,row
from cte c
order by row单号            出入库时间                   出库数量                                    入库数量                                    结存                                      row
------------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------
i1            2010-09-10 00:00:00.000 0.000                                   50.000                                  50.000                                  1
i2            2010-09-11 00:00:00.000 0.000                                   50.000                                  100.000                                 2
o1            2010-09-11 00:00:00.000 10.000                                  0.000                                   90.000                                  3
o2            2010-09-12 00:00:00.000 20.000                                  0.000                                   70.000                                  4
i3            2010-09-13 00:00:00.000 0.000                                   60.000                                  130.000                                 5
o3            2010-09-14 00:00:00.000 60.000                                  0.000                                   70.000                                  6(6 row(s) affected)

Answer 4

insert into b values('i2','aaa','50')
insert into b values('i2','bbb','50')
====================
上面数据是不是有问题，4#中去掉了insert into b values('i2','bbb','50')
这边记录，如果不对，再修正。

Answer 5

忽略5#，数据没有问题，没看到‘aaa’的条件：
修正如下：
;with cte
as
(
select  distinct inno 单号,indate 出入库时间  ,
出库数量=(select  isnull(SUM(QTY),0) FROM #B b where b.inno=m.inno and left(inno,1)='o' and b.pname=m.pname), 
入库数量=(select  isnull(SUM(QTY),0) FROM #B b where b.inno=m.inno and left(inno,1)='i' and b.pname=m.pname) 
--结存=
,m.row
from
(    
SELECT a.inno,a.indate,b.qty,b.pname,row_number()over(order by getdate())row
FROM #A a
left join #B b
ON a.inno=b.inno
where b.pname='aaa'
)M
)
select  单号, 出入库时间,出库数量,入库数量
,结存=(select sum(入库数量-出库数量) from cte where row<=c.row)
,row
from cte c
order by row单号            出入库时间                   出库数量                                    入库数量                                    结存                                      row
------------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------
i1            2010-09-10 00:00:00.000 0.000                                   50.000                                  50.000                                  1
i2            2010-09-11 00:00:00.000 0.000                                   50.000                                  100.000                                 2
o1            2010-09-11 00:00:00.000 10.000                                  0.000                                   90.000                                  3
o2            2010-09-12 00:00:00.000 20.000                                  0.000                                   70.000                                  4
i3            2010-09-13 00:00:00.000 0.000                                   60.000                                  130.000                                 5(5 row(s) affected)

Answer 6

create table a
(
    inno char(13),--出入库单号  出库以o打开,入库以i开头
    indate datetime--出入库时间
)
insert into a values('i1','2010-9-10')
insert into a values('i2','2010-9-11')
insert into a values('o1','2010-9-11')
insert into a values('o2','2010-9-12')
insert into a values('i3','2010-9-13')
insert into a values('o3','2010-9-14')
create table b
(
    inno char(13),--出入库单号
    pname char(13),--物料编号
    qty int--数量
    
)
insert into b values('i1','aaa','50')
insert into b values('i2','aaa','50')
insert into b values('i2','bbb','50')
insert into b values('o1','aaa','10')
insert into b values('o2','aaa','20')
insert into b values('i3','aaa','60')
insert into b values('o3','bbb','60')
godeclare @pname as varchar(10)
set @pname = 'aaa' select t1.inno , t1.indate,
       (case when t1.inno like 'o%' then ltrim(t1.qty) else '' end) 出库数量,
       (case when t1.inno like 'i%' then ltrim(t1.qty) else '' end) 出库数量,
       (select sum(case when inno like 'i%' then qty else -qty end) from (  select  m.*,n.qty , px = (select count(1) from a , b where b.pname=@pname and a.inno = b.inno and (a.indate < m.indate or (a.indate = m.indate and a.inno < m.inno)) ) + 1
  from a m , b n where n.pname=@pname and m.inno = n.inno
) t2 where t2.px <= t1.px)  结存
from
(
  select  m.*,n.qty , px = (select count(1) from a , b where b.pname=@pname and a.inno = b.inno and (a.indate < m.indate or (a.indate = m.indate and a.inno < m.inno)) ) + 1
  from a m , b n where n.pname=@pname and m.inno = n.inno
) t1drop table a , b/*
inno          indate                                                 出库数量         出库数量         结存          
------------- ------------------------------------------------------ ------------ ------------ ----------- 
i1            2010-09-10 00:00:00.000                                             50           50
i2            2010-09-11 00:00:00.000                                             50           100
o1            2010-09-11 00:00:00.000                                10                        90
o2            2010-09-12 00:00:00.000                                20                        70
i3            2010-09-13 00:00:00.000                                             60           130（所影响的行数为 5 行）
*/