请大家一起探讨一下关于库存的结构及实现，效率优先，这个是老话题了

plutu 2011-03-27 01:41:57

看了很多以前的老帖子，感觉不是很系统，所以想发起一个帖子让高手都过了总结一下，争取让斑竹“加精”，我先提个大体需求：在进销存系统中，有一基本功能就是能够让用户查询任意时间段的库存明细帐，库存明细帐包含的信息为：期初库存，本期入库，本期出库，期末库存。在这里我想把问题简单化为一个出入库的流水表，一个库存表，现在的问题是如何设计库存表才能让效率更高？是否还有必要做个按月进行的库存月结表？我原来没做月结表，只有一个库存表，发现数据量大的时候每次都要算期初库存等数据会占用大量时间，总感觉效率不高，我先给出部分数据供大家测试（为了说明问题，把表结构简化了）
出入库流水表
CREATE TABLE churuku(
[ids] [int] IDENTITY (1, 1) NOT NULL ,
[riqi] [datetime] NULL ,
[leibie] [char] (15),
[shuliang] [int] NULL ,
[jine] [decimal](9, 3),[biaozhi] [char] (4)
)
GO

insert into churuku(riqi,leibie,shuliang,jine,biaozhi)
select '2011-01-02','甲产品',100,500,'入库' union all
select '2011-01-02','甲产品',10,50,'入库' union all
select '2011-01-02 ','乙产品',8,80,'入库' union all
select '2011-01-03 ','甲产品',2,10,'出库' union all
select '2011-01-05 ','甲产品',20,100,'入库' union all
select '2011-01-05 ','乙产品',4,40,'出库' union all
select '2011-02-02 ','甲产品',20,100,'入库' union all
select '2011-02-02 ','乙产品',1,10,'出库' union all
select '2011-02-08 ','丙产品',10,500,'入库' union all
select '2011-02-12 ','丁产品',2,16,'入库' union all
select '2011-03-10 ','乙产品',10,100,'入库' union all
select '2011-03-12 ','丙产品',2,100,'出库'

现在的问题是：我现在要按照类别查3月份的库存情况，包括期初库存（3月前的）、当月入库量、当月出库量、当月库存量、当月库存金额，请大家不吝指教，最好给出库存表的设计思想及实现代码，以使包括我在内的有类似需求的后来者参考，谢谢

...全文

220 26 打赏收藏转发到动态举报

写回复

用AI写文章

26 条回复

切换为时间正序

请发表友善的回复…

发表回复

plutu 2011-04-06

打赏
举报

是因为从一月开始统计的，我再研究一下表结构吧，谢谢！

叶子 2011-04-05

打赏
举报

过来帮你再看一下

现在的问题是3月的有两个不显示，问题出在表结构上了

上面的是库存操作表，类别应该有类别表。

你说3月份没有显示甲丁，那一月份也没有要求显示丙丁呀一月份的时候丙丁应该都是0呀。

用类别表做下关联就可以了。

plutu 2011-04-03

打赏
举报

但是我要是查询3月份的库存的话，只有乙丙的信息，没有甲丁的信息，这样查询的结果也不全啊

叶子 2011-04-02

打赏
举报



SELECT  * into #t

FROM    ( SELECT    月份 = CONVERT(VARCHAR(7), riqi, 120) ,

                    类别 = leibie ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '入库' THEN shuliang

                               END), 0) 入库总量 ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '出库' THEN shuliang

                               END), 0) 出库总量 ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '入库' THEN shuliang

                               END) - SUM(CASE biaozhi

                                            WHEN '出库' THEN shuliang

                                          END), 0) 库存总量

          FROM      churuku

          GROUP BY  CONVERT(VARCHAR(7), riqi, 120) ,

                    leibie

          UNION ALL

          SELECT    月份 = CONVERT(VARCHAR(7), riqi, 120) ,

                    '本月合计' ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '入库' THEN shuliang

                               END), 0) 入库总量 ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '出库' THEN shuliang

                               END), 0) 出库总量 ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '入库' THEN shuliang

                               END) - SUM(CASE biaozhi

                                            WHEN '出库' THEN shuliang

                                          END), 0) 库存总量

          FROM      churuku

          GROUP BY  CONVERT(VARCHAR(7), riqi, 120)

        ) aa

ORDER BY 月份 ,

        CHARINDEX('甲', 类别) DESC ,

        CHARINDEX('乙', 类别) DESC ,

        CHARINDEX('丙', 类别) DESC ,

        CHARINDEX('丁', 类别) DESC ,

        CHARINDEX('本', 类别) DESC



select *,(select sum(入库总量) from #t 

where 月份<=t.月份 and 类别=t.类别) as 累计入库

,(select sum(出库总量) from #t 

where 月份<=t.月份 and 类别=t.类别) as 累计出库

,  (select sum(入库总量) from #t 

where 月份<=t.月份 and 类别=t.类别)-

(select sum(出库总量) from #t 

where 月份<=t.月份 and 类别=t.类别) as 当月库存

 from #t t



drop table #t

/*

月份      类别              入库总量        出库总量        库存总量        累计入库        累计出库        当月库存

------- --------------- ----------- ----------- ----------- ----------- ----------- -----------

2011-01 甲产品             130         2           128         130         2           128

2011-01 乙产品             8           4           4           8           4           4

2011-01 本月合计            138         6           132         138         6           132

2011-02 甲产品             20          0           0           150         2           148

2011-02 乙产品             0           1           0           8           5           3

2011-02 丙产品             10          0           0           10          0           10

2011-02 丁产品             2           0           0           2           0           2

2011-02 本月合计            32          1           31          170         7           163

2011-03 乙产品             10          0           0           18          5           13

2011-03 丙产品             0           2           0           10          2           8

2011-03 本月合计            10          2           8           180         9           171



*/

结果都对上了，关于3月份没有甲丁，是因为字段名称是当月入库总量当月出库总量当月库存新增
而3月份甲丁的数据是没有出库入库操作的，所以当月入库出库信息里是没有的，合计里面是已经计算了的。

plutu 2011-04-02

打赏
举报

月份      类别    当月入库  当月出库  库存新增  累计入库  累计出库   当月库存     

------- -------- -------- -------- -------- -------- --------    -------- 

2011-01 甲产品      130      2        128      130      2        128

2011-01 乙产品      8        4        4        8        0        4

2011-01 本月合计     138      6        132      138      6        132

2011-02 甲产品      20       0        20       150      2        148

2011-02 乙产品      0        1        -1       8        5        3

2011-02 丙产品      10       0        10       10       0        10

2011-02 丁产品      2        0        2        2        0        2

2011-02 本月合计     32       1        31       170      7        163 

2011-03 甲产品      0        0        0        150      2        148

2011-03 乙产品      10       0        10       18       5        13

2011-03 丙产品      0        2        -2       10       2        8

2011-03 丁产品      0        0        0        2        0        2

2011-03 本月合计     10       2        8        180      9        171

plutu 2011-04-02

打赏
举报

按月统计全部的结果为：
月份类别当月入库总量当月出库总量当月库存新增累计入库总量累计出库总量当月库存
2011-01 甲产品 130 2 128 130 2 128
2011-01 乙产品 8 4 4 8 0 4
2011-01 本月合计 138 6 132 138 6 132
2011-02 甲产品 20 0 20 150 2 148
2011-02 乙产品 0 1 -1 8 5 3
2011-02 丙产品 10 0 10 10 0 10
2011-02 丁产品 2 0 2 2 0 2
2011-02 本月合计 32 1 31 170 7 163
2011-03 甲产品 0 0 0 150 2 148
2011-03 乙产品 10 0 10 18 5 13
2011-03 丙产品 0 2 -2 10 2 8
2011-03 丁产品 0 0 0 2 0 2
2011-03 本月合计 10 2 8 180 9 171
注意：三月份没有进行甲产品和丁产品的操作，但因为原来有出入库，因此也要统计上

如果只查询2011-03月份的结果应该是：
月份类别当月入库总量当月出库总量当月库存新增累计入库总量累计出库总量当月库存
2011-03 甲产品 0 0 0 150 2 148
2011-03 乙产品 10 0 10 18 5 13
2011-03 丙产品 0 2 -2 10 2 8
2011-03 丁产品 0 0 0 2 0 2
2011-03 本月合计 10 2 8 180 9 171

plutu 2011-04-02

打赏
举报

谢谢！倒数第二个6应该是2+4，是我弄错了，150是甲产品1月份入库了130,2月份入库了20，因此截至到2月底的累计入库为130+20=150，累计入库8是因为乙产品在1月份入库了8，二月份只出库了1，没有入库，因此到二月底累计入库了8。你的答案是只考虑了本月有出入库操作的类别，没有考虑本月之前有库存但本月没有操作的类别，比如2011-03月底的库存肯定甲乙丙丁产品都有，但你的三月份库存中没有甲和丁产品，上段代码应该再修改一下才能达到我的要求，请再继续发动脑筋，谢谢！

plutu 2011-04-02

打赏
举报

谢谢楼上的，我等会整理出来

叶子 2011-04-02

打赏
举报

[Quote=引用 16 楼 plutu 的回复:]
今天大家都放假了吗？好像在线的人不多
[/Quote]
你要什么样的结果，根据上面的数据，你给出参数，给出结果，然后我晚上有时间过来再帮你看下。

plutu 2011-04-02

打赏
举报

今天大家都放假了吗？好像在线的人不多

plutu 2011-04-02

打赏
举报

不好意思，这几天出差，上边的语句还是没有达到我的要求，比如我要查三月份的库存情况，应该把三月当月按照类别统计入库量、出库量、之前累积的入库总量之前累积的出库总量、现在的库存都统计出来，还应该包括原来入出库（当月没有此类别操作的），还要加个当月出库和入库及总量合计，最后还要加个总合计

叶子 2011-04-02

打赏
举报



/*

月份      类别    当月入库  当月出库  库存新增  累计入库  累计出库   当月库存     

------- -------- -------- -------- -------- -------- --------    -------- 

2011-01 甲产品      130      2        128      130      2        128

2011-01 乙产品      8        4        4        8        0        4

2011-01 本月合计     138      6        132      138      6        132      --倒数第二个6为什么是上面2+0的和

2011-02 甲产品      20       0        20       150      2        148  --150是怎么算出来的

2011-02 乙产品      0        1        -1       8        5        3    --累计入库的8是怎么算出来的？

2011-02 丙产品      10       0        10       10       0        10

2011-02 丁产品      2        0        2        2        0        2

2011-02 本月合计     32       1        31       170      7        163 

2011-03 甲产品      0        0        0        150      2        148

2011-03 乙产品      10       0        10       18       5        13

2011-03 丙产品      0        2        -2       10       2        8

2011-03 丁产品      0        0        0        2        0        2

2011-03 本月合计     10       2        8        180      9        171





*/



SELECT  *

FROM    ( SELECT    月份 = CONVERT(VARCHAR(7), riqi, 120) ,

                    类别 = leibie ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '入库' THEN shuliang

                               END), 0) 入库总量 ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '出库' THEN shuliang

                               END), 0) 出库总量 ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '入库' THEN shuliang

                               END) - SUM(CASE biaozhi

                                            WHEN '出库' THEN shuliang

                                          END), 0) 库存总量

          FROM      churuku

          GROUP BY  CONVERT(VARCHAR(7), riqi, 120) ,

                    leibie

          UNION ALL

          SELECT    月份 = CONVERT(VARCHAR(7), riqi, 120) ,

                    '本月合计' ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '入库' THEN shuliang

                               END), 0) 入库总量 ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '出库' THEN shuliang

                               END), 0) 出库总量 ,

                    ISNULL(SUM(CASE biaozhi

                                 WHEN '入库' THEN shuliang

                               END) - SUM(CASE biaozhi

                                            WHEN '出库' THEN shuliang

                                          END), 0) 库存总量

          FROM      churuku

          GROUP BY  CONVERT(VARCHAR(7), riqi, 120)

        ) aa

ORDER BY 月份 ,

        CHARINDEX('甲', 类别) DESC ,

        CHARINDEX('乙', 类别) DESC ,

        CHARINDEX('丙', 类别) DESC ,

        CHARINDEX('丁', 类别) DESC ,

        CHARINDEX('本', 类别) DESC

        

/* 现在的结果你的前面我是可以对上的，后面的关系不清楚

        月份      类别              入库总量        出库总量        库存总量

------- --------------- ----------- ----------- -----------

2011-01 甲产品             130         2           128

2011-01 乙产品             8           4           4

2011-01 本月合计            138         6           132

2011-02 甲产品             20          0           0

2011-02 乙产品             0           1           0

2011-02 丙产品             10          0           0

2011-02 丁产品             2           0           0

2011-02 本月合计            32          1           31

2011-03 乙产品             10          0           0

2011-03 丙产品             0           2           0

2011-03 本月合计            10          2           8

 */

AcHerat 2011-03-29

打赏
举报

出入库，盘点，调拨。。。

进销存还是要看你具体的业务，通用的不实用。

plutu 2011-03-28

打赏
举报

这个看似简单的问题其实是比较复杂的，如果小系统简单的几条语句可以实现，但如果数据动辄就上百万条，比如一个大型的连锁超市的库存管理首先要考虑的是效率问题了，大家继续发表自己的看法

Q315054403 2011-03-28

打赏
举报

这个不是三五句能说清楚的，涉及的代码还是有好几十行的

昵称被占用了 2011-03-28

打赏
举报

月结表是必须的

王向飞 2011-03-28

打赏
举报

具体业务具体分析

还是那句话看需求

还有日结月结，盘亏，盘盈。。。。

这里讨论实在没必要，还不如找个仓库管理员懂得多呢。

--小F-- 2011-03-28

打赏
举报

出入库本来就是这样的

进销存是个难题

dawugui 2011-03-27

打赏
举报

哪有那么复杂,你去下个ERP和进销存系统,看看就行了.

叶子 2011-03-27

打赏
举报



;with maco as(

select 

月份=convert(varchar(7),riqi,120),

sum(case biaozhi when '入库' then shuliang end) 当月入库总量,

sum(case biaozhi when '出库' then shuliang end) 当月出库总量,

sum(case biaozhi when '入库' then shuliang end)-

sum(case biaozhi when '出库' then shuliang end) 当月库存新增

from churuku

group by convert(varchar(7),riqi,120))



select *,(select sum(当月入库总量) from maco where 月份<=t.月份) as 之前累积的入库总量,

(select sum(当月出库总量) from maco where 月份<=t.月份) as 之前累积的出库总量,

(select sum(当月库存新增) from maco where 月份<=t.月份) as 现在的库存

 from maco t

/*

月份      当月入库总量      当月出库总量      当月库存新增      之前累积的入库总量   之前累积的出库总量   现在的库存

------- ----------- ----------- ----------- ----------- ----------- -----------

2011-01 138         6           132         138         6           132

2011-02 32          1           31          170         7           163

2011-03 10          2           8           180         9           171

*/