大家看看这个sql该怎么写?
--表结构
create table test --出入库表
(
id int identity(1,1), --主键
货物类别 varchar(20),
货物名称 varchar(20),
货物规格 varchar(20),
货物价格 money,
出入库日期 datetime,
数量 int,
入库单编号 varchar(20),
出库单编号 varchar(20),
退库单编号 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 ',null,null, '1 ',null,null
insert into test select 'a类 ', 'a1 ', '1*2 ',1500, '2007-1-1 ',15, 'rk-2 ',null,null, '1 ',null,null
insert into test select 'a类 ', 'a2 ', '1*3 ',2000, '2007-1-1 ',20, 'rk-3 ',null,null, '1 ',null,null
--以下是出库数据
insert into test select 'a类 ', 'a1 ', '1*2 ',1000, '2007-1-1 ',5,null, 'ck-1 ',null, '0 ', '部门1 ', '张三 '
insert into test select 'a类 ', 'a1 ', '1*2 ',1500, '2007-1-1 ',10,null, 'ck-2 ',null, '0 ', '部门1 ', '张三 '
insert into test select 'a类 ', 'a2 ', '1*3 ',2000, '2007-1-1 ',10,null, 'ck-3 ',null, '0 ', '部门1 ', '张三 '
--以下是出库退货数据
insert into test select 'a类 ', 'a1 ', '1*2 ',1000, '2007-1-1 ',1,null,null, 'tk-1 ', '2 ', '部门1 ', '张三 '
insert into test select 'a类 ', 'a1 ', '1*2 ',1500, '2007-1-1 ',2,null,null, 'tk-2 ', '2 ', '部门1 ', '张三 '
insert into test select 'a类 ', 'a2 ', '1*3 ',2000, '2007-1-1 ',3,null,null, 'tk-3 ', '2 ', '部门1 ', '张三 '
select * from test
drop table test
/*
报表：货物出入库明细
货物类别 货物名称 货物规格 货物价格 入库单据号 入库数量 入库金额 出库单据号 出库数量 出库金额 退库单据号 退库数量 退库金额 领用部门 领用人 日期 结余数量 结余金额
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
*/
[解决办法]
select *,0 as jy into #t from test
declare @i float
set @i=0
update #t set jy=(case when 出入库状态=0 then -1 else 1 end)*数量*货物价格+@i,@i=jy
[解决办法]
select 货物类别,货物名称,货物规格,货物价格,
case 出入库状态 when 1 then 入库单编号 end as 入库单编号,
case 出入库状态 when 1 then 数量 end as 入库数量,
case 出入库状态 when 1 then 货物价格 * 数量 end as 入库金额,
case 出入库状态 when 0 then 出库单编号 end as 出库单编号,
case 出入库状态 when 0 then 数量 end as 出库数量,
case 出入库状态 when 0 then 货物价格 * 数量 end as 出库金额,
case 出入库状态 when 2 then 退库单编号 end as 退库单编号,
case 出入库状态 when 2 then 数量 end as 退库数量,
case 出入库状态 when 2 then 货物价格 * 数量 end as 退库金额,
领用部门,领用人,convert(varchar(10),出入库日期,120) as 日期,
结余数量 = (select sum(case 出入库状态 when 0 then - 数量 else 数量 end) from test
where 货物名称 = a.货物名称 and 货物规格 = a.货物规格 and 货物价格 = a.货物价格 and id <= a.id),
结余金额 = (select sum(case 出入库状态 when 0 then - 货物价格* 数量 else 货物价格* 数量 end) from test
where 货物名称 = a.货物名称 and 货物规格 = a.货物规格 and 货物价格 = a.货物价格 and id <= a.id)
from test a
--结果
a类a11*21000.0000rk-11010000.0000NULLNULLNULLNULLNULLNULLNULLNULL2007-01-011010000.0000
a类a11*21500.0000rk-21522500.0000NULLNULLNULLNULLNULLNULLNULLNULL2007-01-011522500.0000
a类a21*32000.0000rk-32040000.0000NULLNULLNULLNULLNULLNULLNULLNULL2007-01-012040000.0000
a类a11*21000.0000NULLNULLNULLck-155000.0000NULLNULLNULL部门1张三2007-01-0155000.0000
a类a11*21500.0000NULLNULLNULLck-21015000.0000NULLNULLNULL部门1张三2007-01-0157500.0000
a类a21*32000.0000NULLNULLNULLck-31020000.0000NULLNULLNULL部门1张三2007-01-011020000.0000
a类a11*21000.0000NULLNULLNULLNULLNULLNULLtk-111000.0000部门1张三2007-01-0166000.0000
a类a11*21500.0000NULLNULLNULLNULLNULLNULLtk-223000.0000部门1张三2007-01-01710500.0000
a类a21*32000.0000NULLNULLNULLNULLNULLNULLtk-336000.0000部门1张三2007-01-011326000.0000
[解决办法]
create table test --出入库表
(
id int identity(1,1), --主键
货物类别 varchar(20),
货物名称 varchar(20),
货物规格 varchar(20),
货物价格 money,
出入库日期 datetime,
数量 int,
入库单编号 varchar(20),
出库单编号 varchar(20),
退库单编号 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 ',null,null, '1 ',null,null
insert into test select 'a类 ', 'a1 ', '1*2 ',1500, '2007-1-1 ',15, 'rk-2 ',null,null, '1 ',null,null
insert into test select 'a类 ', 'a2 ', '1*3 ',2000, '2007-1-1 ',20, 'rk-3 ',null,null, '1 ',null,null
--以下是出库数据
insert into test select 'a类 ', 'a1 ', '1*2 ',1000, '2007-1-1 ',5,null, 'ck-1 ',null, '0 ', '部门1 ', '张三 '
insert into test select 'a类 ', 'a1 ', '1*2 ',1500, '2007-1-1 ',10,null, 'ck-2 ',null, '0 ', '部门1 ', '张三 '
insert into test select 'a类 ', 'a2 ', '1*3 ',2000, '2007-1-1 ',10,null, 'ck-3 ',null, '0 ', '部门1 ', '张三 '
--以下是出库退货数据
insert into test select 'a类 ', 'a1 ', '1*2 ',1000, '2007-1-1 ',1,null,null, 'tk-1 ', '2 ', '部门1 ', '张三 '
insert into test select 'a类 ', 'a1 ', '1*2 ',1500, '2007-1-1 ',2,null,null, 'tk-2 ', '2 ', '部门1 ', '张三 '
insert into test select 'a类 ', 'a2 ', '1*3 ',2000, '2007-1-1 ',3,null,null, 'tk-3 ', '2 ', '部门1 ', '张三 '
select 货物类别, 货物名称, 货物规格, 货物价格,
入库单据号=入库单编号,
入库数量=case when 出入库状态=1 then 数量 else 0 end,
入库金额=case when 出入库状态=1 then 数量*货物价格 else 0 end,
出库单据号=出库单编号,
出库数量=case when 出入库状态=0 then 数量 else 0 end,
出库金额=case when 出入库状态=0 then 数量*货物价格 else 0 end,
退库单据号=退库单编号,
退库数量=case when 出入库状态=2 then 数量 else 0 end,
退库金额=case when 出入库状态=2 then 数量*货物价格 else 0 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 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
(9 row(s) affected)
