销售-库存先进先出查询方法

最新推荐文章于 2021-01-29 10:41:18 发布

原创最新推荐文章于 2021-01-29 10:41:18 发布 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#insert #产品 #table #sql server #测试

SQL Server 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询用于计算特定产品的销售成本，采用先进先出的方法，涉及两个表格的数据处理与关联。

有一个表 #t 如下:
产品id     产品名称   购进数量    购进单价   购进日期
   1         aa         100          10        2007-1-1
   1         aa          50          20        2007-1-15
   1         aa          100         22        2007-1-20

有一个表 #p 如下:
产品id     产品名称   销售数量      销售日期
   1         aa                 50               2007-1-2
   1         aa                 60                  2007-1-16
   1         aa                 10                  2007-1-21
   1         aa                 40                  2007-1-22

--要产生下面的统计结果
产品id     产品名称   销售数量    销售成本      销售日期
   1         aa         50         50*10(购进单价)       2007-1-2
   1         aa          60        50*10+10*20            2007-1-16
   1         aa          10         10*20                        2007-1-21
   1         aa          40         30*20+10*22          2007-1-22

就是所谓的先购进的先销售

--建立环境
create table #t
(
pid int,
pname varchar(10),
jsum int,
jpri int,
jdate datetime
)

create table #p
(
pid int,
pname varchar(10),
xnum int,
xdate smalldatetime
)

insert into #t select    1,         'aa',         100,          10,        '2007-1-1'
insert into #t select    1,         'aa',          50,          20,        '2007-1-15'
insert into #t select    1,         'aa',         100,          22,        '2007-1-20'

insert into #p select    1,         'aa',         50,    '2007-1-2'
insert into #p select    1,         'aa',         60,   '2007-1-16'
insert into #p select    1,         'aa',         10,   '2007-1-21'
insert into #p select    1,         'aa',         40,   '2007-1-22'

--语句
select pp.pid,pp.pname,pp.xnum,
cb = sum(case when qxnum >= qjnum and xxnum <= xjnum then xnum * jpri
           when qxnum >= qjnum and xxnum >= xjnum then (xjnum - qxnum) * jpri
           when qxnum <= qjnum and xxnum <= xjnum then (xxnum - qjnum) * jpri
      end),
pp.xdate
from
(select pid,pname,jsum,
qjnum = (select isnull(sum(jsum),0) from #t where pid = t.pid and jdate < t.jdate),
xjnum = (select sum(jsum) from #t where pid = t.pid and jdate <= t.jdate),
       jpri,jdate
from #t t) tt,
(select pid,pname,xnum,xdate,
qxnum = (select isnull(sum(xnum),0) from #p where pid = p.pid and xdate < p.xdate),
xxnum = (select sum(xnum) from #p where pid = p.pid and xdate <= p.xdate)
from #p p ) pp
where qxnum between qjnum and xjnum or xxnum between qjnum and xjnum
group by pp.pid,pp.pname,pp.xnum,pp.xdate
order by xdate

--结果
pid         pname      xnum        cb          xdate
----------- ---------- ----------- ----------- -----------------------
1           aa         50          500         2007-01-02 00:00:00
1           aa         60          700         2007-01-16 00:00:00
1           aa         10          200         2007-01-21 00:00:00
1           aa         40          820         2007-01-22 00:00:00

(4 行受影响)

--删除环境
drop table #t
drop table #p

(Win2003 + SQL Server 2005 下测试通过)