求物流仓库管理的SQL语句?谢谢大家.
/*如何生成库存明细帐和库存盘点表*/
--表结构
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 40000
a类 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 20000
a类 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-1
a类 a1 1*2 1500 7 10500 2007-1-1
a类 a2 1*3 2000 13 26000 2007-1-1
*/
/*
说明:
货物出入库明细帐能够按照日期段进行查询
货物库存盘点在任何一天都能得到库存的准确数字.比如,2007-1-2这一天没有出入库记录,那么在2007-1-2进行
查询的时候,也能够得出以上报表数据
*/
[解决办法]
--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
[解决办法]
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类a11*21000.0000rk-11010000.0000NULL0.0000NULL0.0000NULLNULL2007-01-01 00:00:00.0001010000.0000
a类a11*21500.0000rk-21522500.0000NULL0.0000NULL0.0000NULLNULL2007-01-01 00:00:00.0001522500.0000
a类a21*32000.0000rk-32040000.0000NULL0.0000NULL0.0000NULLNULL2007-01-01 00:00:00.0002040000.0000
a类a11*21000.0000NULL0.0000ck-155000.0000NULL0.0000部门1张三2007-01-01 00:00:00.00055000.0000
a类a11*21500.0000NULL0.0000ck-21015000.0000NULL0.0000部门1张三2007-01-01 00:00:00.00057500.0000
a类a21*32000.0000NULL0.0000ck-31020000.0000NULL0.0000部门1张三2007-01-01 00:00:00.0001020000.0000
a类a11*21000.0000NULL0.0000NULL0.0000tk-111000.0000部门1张三2007-01-01 00:00:00.00066000.0000
a类a11*21500.0000NULL0.0000NULL0.0000tk-223000.0000部门1张三2007-01-01 00:00:00.000710500.0000
a类a21*32000.0000NULL0.0000NULL0.0000tk-336000.0000部门1张三2007-01-01 00:00:00.0001326000.0000
*/
