求物流仓库管理的SQL语句?谢谢大家.

Question

/*如何生成库存明细帐和库存盘点表*/--表结构create table test --出入库表
(
id int identity(1,1), --主键
货物类别  varchar(20),
货物名称  varchar(20),
货物规格  varchar(20),
货物价格  money,
出入库日期 datetime,
数量  int,
单据编号 varchar(20),
出入库状态 char(1),--1代表货物入库,0代表货物出库,2代表出库退货
领用部门 varchar(20),
领用人 varchar(20)
)
--以下是入库数据
insert into test select 'a类','a1','1*2',1000,'2007-1-1',10,'rk-1','1',null,null
insert into test select 'a类','a1','1*2',1500,'2007-1-1',15,'rk-2','1',null,null
insert into test select 'a类','a2','1*3',2000,'2007-1-1',20,'rk-3','1',null,null--以下是出库数据insert into test select 'a类','a1','1*2',1000,'2007-1-1',5,'ck-1','0','部门1','张三'
insert into test select 'a类','a1','1*2',1500,'2007-1-1',10,'ck-2','0','部门1','张三'
insert into test select 'a类','a2','1*3',2000,'2007-1-1',10,'ck-3','0','部门1','张三'--以下是出库退货数据
insert into test select 'a类','a1','1*2',1000,'2007-1-1',1,'tk-1','2','部门1','张三'
insert into test select 'a类','a1','1*2',1500,'2007-1-1',2,'tk-2','2','部门1','张三'
insert into test select 'a类','a2','1*3',2000,'2007-1-1',3,'tk-3','2','部门1','张三'
select * from test
drop table test
/*
要求得到以下两种格式的库存帐
1.货物出入库明细货物类别  货物名称  货物规格   货物价格   入库单据号  入库数量  入库金额   出库单据号  出库数量  出库金额  退库单据号  退库数量  退库金额  领用部门  领用人   日期     结余数量  结余金额   
a类   a1       1*2       1000       rk-1       10        10000      null       0         0        null        0          0     null     null    2007-1-1  10       10000
a类       a1       1*2       1500       rk-2       15        22500      null       0         0        null        0          0     null     null    2007-1-1  15       22500
a类       a2       1*3       2000       rk-3       20        40000      null       0         0        null        0          0     null     null    2007-1-1  20       40000a类       a1       1*2       1000       null       0         0          ck-1       5         5000     null        0          0     部门1    张三    2007-1-1   5        5000
a类       a1       1*2       1500       null       0         0          ck-2       10       15000     null        0          0     部门1    张三    2007-1-1   5        7500
a类       a2       1*3       2000       null       0         0          ck-3       10       20000     null        0          0     部门1    张三    2007-1-1   10       20000a类       a1       1*2       1000       null       0         0          null       0         0        tk-1        1        1000    部门1    张三    2007-1-1   6        6000
a类       a1       1*2       1500       null       0         0          null       0         0        tk-2        2        3000    部门1    张三    2007-1-1   7        10500
a类       a2       1*3       2000       null       0         0          null       0         0        tk-3        3        6000    部门1    张三    2007-1-1   13       26000     
2.货物库存盘点货物类别    货物名称   货物规格    货物价格    库存结余数量   库存结余金额   日期a类         a1         1*2        1000        6             6000          2007-1-1a类         a1         1*2        1500        7             10500         2007-1-1a类         a2         1*3        2000        13            26000         2007-1-1   
*//*说明: 货物出入库明细帐能够按照日期段进行查询
货物库存盘点在任何一天都能得到库存的准确数字.比如,2007-1-2这一天没有出入库记录,那么在2007-1-2进行
查询的时候,也能够得出以上报表数据*/

Answer 1

--1
select 货物类别, 货物名称, 货物规格, 货物价格, 
入库单据号=case when 出入库状态=1 then 单据编号 end, 入库数量=case when 出入库状态=1 then 数量 end, 入库金额=case when 出入库状态=1 then 货物价格*数量 end,
出库单据号=case when 出入库状态=0 then 单据编号 end,  出库数量=case when 出入库状态=0 then 数量 end,  出库金额=case when 出入库状态=0 then 货物价格*数量 end,
退库单据号=case when 出入库状态=2 then 单据编号 end,   退库数量=case when 出入库状态=2 then 数量 end,    退库金额=case when 出入库状态=2 then 货物价格*数量 end,
领用部门,  领用人,  日期=出入库日期, 结余数量=数量, 结余金额=货物价格*数量 
from test as A

Answer 2

--2
select 货物类别, 货物名称, 货物规格, 货物价格, 库存结余数量=sum(case when 出入库状态=0 then -数量 else 数量  end),
库存结余金额=sum(case when 出入库状态=0 then -数量 else 数量  end)*货物价格, 日期=max(出入库日期)
from test 
group by 货物类别, 货物名称, 货物规格, 货物价格
--result
货物类别                 货物名称                 货物规格                 货物价格                  库存结余数量      库存结余金额                日期                                                     
-------------------- -------------------- -------------------- --------------------- ----------- --------------------- ------------------------------------------------------ 
a类                   a1                   1*2                  1000.0000             6           6000.0000             2007-01-01 00:00:00.000
a类                   a1                   1*2                  1500.0000             7           10500.0000            2007-01-01 00:00:00.000
a类                   a2                   1*3                  2000.0000             13          26000.0000            2007-01-01 00:00:00.000(3 row(s) affected)

Answer 3

marco08(天道酬勤) 
-------------------------------------
出库退货的结余数=上次的结余数+退货数其实出库退货就是再入库的过程.

Answer 4

marco08(天道酬勤)
--------------------------------
第一个问题的结余数量没有问题,其实第一个问题的结余数与第二个问题的结余数是一样的.都是6,7,13

Answer 5

整个明细帐的过程是按照批次走的,也就是按照test表里面的id列递增下来的.结余数量=(入库数量 + 退库数量)-出库数量

Answer 6

select 货物类别,  货物名称,  货物规格 ,  货物价格 ,  
入库单据号=max(case 出入库状态 when 1 then 单据编号 end),  入库数量=sum(case 出入库状态 when 1 then 数量 else 0 end),  入库金额=sum(case 出入库状态 when 1 then 货物价格*数量 else 0 end), 
出库单据号=max(case 出入库状态 when 0 then 单据编号 end),  出库数量=sum(case 出入库状态 when 0 then 数量 else 0 end),  出库金额=sum(case 出入库状态 when 0 then 货物价格*数量 else 0 end),  
退库单据号=max(case 出入库状态 when 2 then 单据编号 end),  退库数量=sum(case 出入库状态 when 2 then 数量 else 0 end),  退库金额=sum(case 出入库状态 when 2 then 货物价格*数量 else 0 end),  
领用部门,  领用人,   日期=出入库日期,
结余数量=(select sum(case when 出入库状态 in(1,2) then 数量 else -数量 end)from test_t where id!>ta.id and 货物名称=ta.货物名称 and 货物规格=ta.货物规格 and 货物价格=ta.货物价格),
结余金额=(select sum(case when 出入库状态 in(1,2) then 数量*货物价格 else -数量*货物价格 end)from test_t where id!>ta.id and 货物名称=ta.货物名称 and 货物规格=ta.货物规格 and 货物价格=ta.货物价格)
from test_t  ta
group by ta.id,货物类别,  货物名称,  货物规格 ,  货物价格 , 领用部门,  领用人,   出入库日期
/*
a类 a1 1*2 1000.0000 rk-1 10 10000.0000 NULL 0 .0000 NULL 0 .0000 NULL NULL 2007-01-01 00:00:00.000 10 10000.0000
a类 a1 1*2 1500.0000 rk-2 15 22500.0000 NULL 0 .0000 NULL 0 .0000 NULL NULL 2007-01-01 00:00:00.000 15 22500.0000
a类 a2 1*3 2000.0000 rk-3 20 40000.0000 NULL 0 .0000 NULL 0 .0000 NULL NULL 2007-01-01 00:00:00.000 20 40000.0000
a类 a1 1*2 1000.0000 NULL 0 .0000 ck-1 5 5000.0000 NULL 0 .0000 部门1 张三 2007-01-01 00:00:00.000 5 5000.0000
a类 a1 1*2 1500.0000 NULL 0 .0000 ck-2 10 15000.0000 NULL 0 .0000 部门1 张三 2007-01-01 00:00:00.000 5 7500.0000
a类 a2 1*3 2000.0000 NULL 0 .0000 ck-3 10 20000.0000 NULL 0 .0000 部门1 张三 2007-01-01 00:00:00.000 10 20000.0000
a类 a1 1*2 1000.0000 NULL 0 .0000 NULL 0 .0000 tk-1 1 1000.0000 部门1 张三 2007-01-01 00:00:00.000 6 6000.0000
a类 a1 1*2 1500.0000 NULL 0 .0000 NULL 0 .0000 tk-2 2 3000.0000 部门1 张三 2007-01-01 00:00:00.000 7 10500.0000
a类 a2 1*3 2000.0000 NULL 0 .0000 NULL 0 .0000 tk-3 3 6000.0000 部门1 张三 2007-01-01 00:00:00.000 13 26000.0000
*/

Answer 7

select 货物类别, 货物名称, 货物规格, 货物价格, 
入库单据号=case when 出入库状态=1 then 单据编号 end, 入库数量=case when 出入库状态=1 then 数量 end, 入库金额=case when 出入库状态=1 then 货物价格*数量 end,
出库单据号=case when 出入库状态=0 then 单据编号 end,  出库数量=case when 出入库状态=0 then 数量 end,  出库金额=case when 出入库状态=0 then 货物价格*数量 end,
退库单据号=case when 出入库状态=2 then 单据编号 end,   退库数量=case when 出入库状态=2 then 数量 end,    退库金额=case when 出入库状态=2 then 货物价格*数量 end,
领用部门,  领用人,  日期=出入库日期, 
结余数量=(select sum(case when 出入库状态=0 then -数量  else 数量 end) from test where id<=A.id and 
货物类别=A.货物类别 and 货物名称=A.货物名称 and 货物规格=A.货物规格  and 货物价格=A.货物价格), 
结余金额=货物价格*(select sum(case when 出入库状态=0 then -数量  else 数量 end) from test where id<=A.id and 
货物类别=A.货物类别 and 货物名称=A.货物名称 and 货物规格=A.货物规格  and 货物价格=A.货物价格) 
from test as A--result
货物类别                 货物名称                 货物规格                 货物价格                  入库单据号                入库数量        入库金额                  出库单据号                出库数量        出库金额                  退库单据号                退库数量        退库金额                  领用部门                 领用人                  日期                                                     结余数量        结余金额                  
-------------------- -------------------- -------------------- --------------------- -------------------- ----------- --------------------- -------------------- ----------- --------------------- -------------------- ----------- --------------------- -------------------- -------------------- ------------------------------------------------------ ----------- --------------------- 
a类                   a1                   1*2                  1000.0000             rk-1                 10          10000.0000            NULL                 NULL        NULL                  NULL                 NULL        NULL                  NULL                 NULL                 2007-01-01 00:00:00.000                                10          10000.0000
a类                   a1                   1*2                  1500.0000             rk-2                 15          22500.0000            NULL                 NULL        NULL                  NULL                 NULL        NULL                  NULL                 NULL                 2007-01-01 00:00:00.000                                15          22500.0000
a类                   a2                   1*3                  2000.0000             rk-3                 20          40000.0000            NULL                 NULL        NULL                  NULL                 NULL        NULL                  NULL                 NULL                 2007-01-01 00:00:00.000                                20          40000.0000
a类                   a1                   1*2                  1000.0000             NULL                 NULL        NULL                  ck-1                 5           5000.0000             NULL                 NULL        NULL                  部门1                  张三                   2007-01-01 00:00:00.000                                5           5000.0000
a类                   a1                   1*2                  1500.0000             NULL                 NULL        NULL                  ck-2                 10          15000.0000            NULL                 NULL        NULL                  部门1                  张三                   2007-01-01 00:00:00.000                                5           7500.0000
a类                   a2                   1*3                  2000.0000             NULL                 NULL        NULL                  ck-3                 10          20000.0000            NULL                 NULL        NULL                  部门1                  张三                   2007-01-01 00:00:00.000                                10          20000.0000
a类                   a1                   1*2                  1000.0000             NULL                 NULL        NULL                  NULL                 NULL        NULL                  tk-1                 1           1000.0000             部门1                  张三                   2007-01-01 00:00:00.000                                6           6000.0000
a类                   a1                   1*2                  1500.0000             NULL                 NULL        NULL                  NULL                 NULL        NULL                  tk-2                 2           3000.0000             部门1                  张三                   2007-01-01 00:00:00.000                                7           10500.0000
a类                   a2                   1*3                  2000.0000             NULL                 NULL        NULL                  NULL                 NULL        NULL                  tk-3                 3           6000.0000             部门1                  张三                   2007-01-01 00:00:00.000                                13          26000.0000(9 row(s) affected)

Answer 8

以上语句改一下楼主的表名,把test_t 改为test就行了。
create table test_t --出入库表
(
id int identity(1,1), --主键
货物类别  varchar(20),
货物名称  varchar(20),
货物规格  varchar(20),
货物价格  money,
出入库日期 datetime,
数量  int,
单据编号 varchar(20),
出入库状态 char(1),--1代表货物入库,0代表货物出库,2代表出库退货
领用部门 varchar(20),
领用人 varchar(20)
)
--以下是入库数据
insert into test_t select 'a类','a1','1*2',1000,'2007-1-1',10,'rk-1','1',null,null
insert into test_t select 'a类','a1','1*2',1500,'2007-1-1',15,'rk-2','1',null,null
insert into test_t select 'a类','a2','1*3',2000,'2007-1-1',20,'rk-3','1',null,null--以下是出库数据insert into test_t select 'a类','a1','1*2',1000,'2007-1-1',5,'ck-1','0','部门1','张三'
insert into test_t select 'a类','a1','1*2',1500,'2007-1-1',10,'ck-2','0','部门1','张三'
insert into test_t select 'a类','a2','1*3',2000,'2007-1-1',10,'ck-3','0','部门1','张三'--以下是出库退货数据
insert into test_t select 'a类','a1','1*2',1000,'2007-1-1',1,'tk-1','2','部门1','张三'
insert into test_t select 'a类','a1','1*2',1500,'2007-1-1',2,'tk-2','2','部门1','张三'
insert into test_t select 'a类','a2','1*3',2000,'2007-1-1',3,'tk-3','2','部门1','张三'select 货物类别,  货物名称,  货物规格 ,  货物价格 ,
库存结余数量=(select sum(case when 出入库状态 in(1,2) then 数量 else -数量 end)from test_t where  货物名称=ta.货物名称 and 货物规格=ta.货物规格 and 货物价格=ta.货物价格),
库存结余金额=(select sum(case when 出入库状态 in(1,2) then 数量*货物价格 else -数量*货物价格 end)from test_t where 货物名称=ta.货物名称 and 货物规格=ta.货物规格 and 货物价格=ta.货物价格),
出入库日期 
from test_t ta
group by 货物类别,  货物名称,  货物规格 ,  货物价格 ,出入库日期 
货物类别                 货物名称                 货物规格                 货物价格                  库存结余数量      库存结余金额                出入库日期                                                  
-------------------- -------------------- -------------------- --------------------- ----------- --------------------- ------------------------------------------------------ 
a类                   a1                   1*2                  1000.0000             6           6000.0000             2007-01-01 00:00:00.000
a类                   a1                   1*2                  1500.0000             7           10500.0000            2007-01-01 00:00:00.000
a类                   a2                   1*3                  2000.0000             13          26000.0000            2007-01-01 00:00:00.000（所影响的行数为 3 行）

Answer 9

已测试过答案，楼主可以结贴了
select 货物类别,  货物名称,  货物规格 ,  货物价格 ,  
入库单据号=max(case 出入库状态 when 1 then 单据编号 end),  入库数量=sum(case 出入库状态 when 1 then 数量 else 0 end),  入库金额=sum(case 出入库状态 when 1 then 货物价格*数量 else 0 end), 
出库单据号=max(case 出入库状态 when 0 then 单据编号 end),  出库数量=sum(case 出入库状态 when 0 then 数量 else 0 end),  出库金额=sum(case 出入库状态 when 0 then 货物价格*数量 else 0 end),  
退库单据号=max(case 出入库状态 when 2 then 单据编号 end),  退库数量=sum(case 出入库状态 when 2 then 数量 else 0 end),  退库金额=sum(case 出入库状态 when 2 then 货物价格*数量 else 0 end),  
领用部门,  领用人,   日期=出入库日期,
结余数量=(select sum(case when 出入库状态 in(1,2) then 数量 else -数量 end)from test where id!>ta.id and 货物名称=ta.货物名称 and 货物规格=ta.货物规格 and 货物价格=ta.货物价格),
结余金额=(select sum(case when 出入库状态 in(1,2) then 数量*货物价格 else -数量*货物价格 end)from test where id!>ta.id and 货物名称=ta.货物名称 and 货物规格=ta.货物规格 and 货物价格=ta.货物价格)
from test  ta
group by ta.id,货物类别,  货物名称,  货物规格 ,  货物价格 , 领用部门,  领用人,   出入库日期select 货物类别,  货物名称,  货物规格 ,  货物价格 ,
库存结余数量=(select sum(case when 出入库状态 in(1,2) then 数量 else -数量 end)from test where  货物名称=ta.货物名称 and 货物规格=ta.货物规格 and 货物价格=ta.货物价格),
库存结余金额=(select sum(case when 出入库状态 in(1,2) then 数量*货物价格 else -数量*货物价格 end)from test where 货物名称=ta.货物名称 and 货物规格=ta.货物规格 and 货物价格=ta.货物价格),
出入库日期 
from test ta
group by 货物类别,  货物名称,  货物规格 ,  货物价格 ,出入库日期

Answer 10

列名多,要加一下判断是比较多点,哈哈
楼主可以生成视图,以后查询时就方便多了
在语句前加
create view 视图A
as 以后查询时
select * from 视图A