22,209
社区成员
发帖
与我相关
我的任务
分享
create table 库存表(编码 varchar(10), 库存 int, 进货日期 datetime)
insert into 库存表 values('001' , 10 , '2008-03-01')
insert into 库存表 values('001' , 16 , '2008-03-02')
insert into 库存表 values('001' , 29 , '2008-03-03')
insert into 库存表 values('002' , 12 , '2008-03-08')
insert into 库存表 values('002' , 15 , '2008-03-09')
create table 销售表(编码 varchar(10), 销售数量 int)
insert into 销售表 values('001' , 15 )
insert into 销售表 values('002' , 20 )
go
--查询
select m.编码 ,
库存 = case when (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) < n.销售数量 then 0
when (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) >= n.销售数量 and isnull((select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 < m.进货日期),0) < n.销售数量 then (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) - n.销售数量
else m.库存
end ,
进货日期
from 库存表 m , 销售表 n
where m.编码 = n.编码
drop table 库存表,销售表
先进先出得到以下数据:
001 0 2008-03-01 00:00:00.000
001 11 2008-03-02 00:00:00.000
001 29 2008-03-03 00:00:00.000
002 0 2008-03-08 00:00:00.000
002 7 2008-03-09 00:00:00.000
====================================================================
问题:如果我想得到已经减了的数据怎么写
====================================================================
001 10 2008-03-01 00:00:00.000
001 5 2008-03-02 00:00:00.000
001 0 2008-03-03 00:00:00.000 // 为零的最好可以不显示
002 12 2008-03-08 00:00:00.000
002 8 2008-03-09 00:00:00.000
create table 库存表(编码 varchar(10), 库存 int, 进货日期 datetime)
insert into 库存表 values('001' , 10 , '2008-03-01')
insert into 库存表 values('001' , 16 , '2008-03-02')
insert into 库存表 values('001' , 29 , '2008-03-03')
insert into 库存表 values('002' , 12 , '2008-03-08')
insert into 库存表 values('002' , 15 , '2008-03-09')
create table 销售表(编码 varchar(10), 销售数量 int)
insert into 销售表 values('001' , 15 )
insert into 销售表 values('002' , 20 )
go
--查询
select m.编码 , m.库存 , m.进货日期 ,
case when m.库存总量 <= n.销售数量 then 库存
when m.库存总量 > n.销售数量 and m.库存总量 - m.库存 <= n.销售数量 then n.销售数量 - (m.库存总量 - m.库存)
end 出货量
from
(select t.* , 库存总量 = (select sum(库存) from 库存表 where 编码 = t.编码 and 进货日期 <= t.进货日期) from 库存表 t) m,
销售表 n
where (m.编码 = n.编码) and ((m.库存总量 <= n.销售数量) or (m.库存总量 > n.销售数量 and m.库存总量 - m.库存 <= n.销售数量))
drop table 库存表,销售表
/*
编码 库存 进货日期 出货量
---------- ----------- ------------------------------------------------------ -----------
001 10 2008-03-01 00:00:00.000 10
001 16 2008-03-02 00:00:00.000 5
002 12 2008-03-08 00:00:00.000 12
002 15 2008-03-09 00:00:00.000 8
(所影响的行数为 4 行)
*/
create table 库存表(编码 varchar(10), 库存 int, 进货日期 datetime)
insert into 库存表 values('001' , 10 , '2008-03-01')
insert into 库存表 values('001' , 16 , '2008-03-02')
insert into 库存表 values('001' , 29 , '2008-03-03')
insert into 库存表 values('002' , 12 , '2008-03-08')
insert into 库存表 values('002' , 15 , '2008-03-09')
create table 销售表(编码 varchar(10), 销售数量 int)
insert into 销售表 values('001' , 15 )
insert into 销售表 values('002' , 20 )
go
--查询
select a.编码,a.库存-b.库存 as 出库,a.进货日期 from 库存表 a inner join(
select m.编码 ,
库存 = case when (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) < n.销售数量 then 0
when (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) >= n.销售数量 and isnull((select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 < m.进货日期),0) < n.销售数量 then (select sum(库存) from 库存表 where 编码 = m.编码 and 进货日期 <= m.进货日期) - n.销售数量
else m.库存
end ,
进货日期
from 库存表 m , 销售表 n
where m.编码 = n.编码
)b on a.编码=b.编码 and a.进货日期=b.进货日期
where a.库存<>b.库存
drop table 库存表,销售表
/*
编码 出库 进货日期
---------- ----------- -----------------------
001 10 2008-03-01 00:00:00.000
001 5 2008-03-02 00:00:00.000
002 12 2008-03-08 00:00:00.000
002 8 2008-03-09 00:00:00.000
(4 行受影响)
*/
create table 库存表(编码 varchar(10), 库存 int, 进货日期 datetime,单价 money)
insert into 库存表 values('001' , 10 , '2008-03-01',10)
insert into 库存表 values('001' , 16 , '2008-03-02',9.8)
insert into 库存表 values('001' , 29 , '2008-03-03',11.5)
insert into 库存表 values('002' , 12 , '2008-03-08',5)
insert into 库存表 values('002' , 15 , '2008-03-09',5.2)
create table 销售表(编码 varchar(10), 销售数量 int)
insert into 销售表 values('001' , 15 )
insert into 销售表 values('002' , 20 )
go
-- Final Query
select z.编码, '平均单价'= sum(出库*单价)/sum(出库), '出库金额' = SUM(出库*单价)
from (
Select x.*,
'出库' = case when 累计销售>=累计库存 then 库存 when 累计销售<累计库存-库存 then 0 else 累计销售 -(累计库存-库存) end,
'结存' = case when 累计销售>=累计库存 then 0 when 累计销售<=累计库存-库存 then 库存 else 累计库存-累计销售 end
from ( select a.*, 累计库存 =(select sum(库存) from 库存表 where 进货日期<= A.进货日期 and 编码 = a.编码) from 库存表 as a) as X
join ( select 编码, 累计销售 = sum(销售数量) from 销售表 group by 编码) as Y
on x.编码 = y.编码
) as Z
where 出库>0
group by z.编码
create table 库存表(编码 varchar(10), 库存 int, 进货日期 datetime,单价 money)
insert into 库存表 values('001' , 10 , '2008-03-01',10)
insert into 库存表 values('001' , 16 , '2008-03-02',9.8)
insert into 库存表 values('001' , 29 , '2008-03-03',11.5)
insert into 库存表 values('002' , 12 , '2008-03-08',5)
insert into 库存表 values('002' , 15 , '2008-03-09',5.2)
create table 销售表(编码 varchar(10), 销售数量 int)
insert into 销售表 values('001' , 15 )
insert into 销售表 values('002' , 20 )
go
-- Query for your fist question, you can use where clause (where 出库>0) at the end to filter out these records.
Select *
from (
Select x.*,
'出库' = case when 累计销售>=累计库存 then 库存 when 累计销售<累计库存-库存 then 0 else 累计销售 -(累计库存-库存) end,
'结存' = case when 累计销售>=累计库存 then 0 when 累计销售<=累计库存-库存 then 库存 else 累计库存-累计销售 end
from ( select a.*, 累计库存 =(select sum(库存) from 库存表 where 进货日期<= A.进货日期 and 编码 = a.编码) from 库存表 as a) as X
join ( select 编码, 累计销售 = sum(销售数量) from 销售表 group by 编码) as Y
on x.编码 = y.编码
) as Z
-- Final Query
select z.编码, sum(出库*单价)/sum(出库), SUM(出库*单价)
from (
Select x.*,
'出库' = case when 累计销售>=累计库存 then 库存 when 累计销售<累计库存-库存 then 0 else 累计销售 -(累计库存-库存) end,
'结存' = case when 累计销售>=累计库存 then 0 when 累计销售<=累计库存-库存 then 库存 else 累计库存-累计销售 end
from ( select a.*, 累计库存 =(select sum(库存) from 库存表 where 进货日期<= A.进货日期 and 编码 = a.编码) from 库存表 as a) as X
join ( select 编码, 累计销售 = sum(销售数量) from 销售表 group by 编码) as Y
on x.编码 = y.编码
) as Z
where 出库>0
group by z.编码
;with wsp
as
(
if @i=1
select * from t1
if @i=2
select * from t2
)
create table 库存表(编码 varchar(10), 库存 int, 进货日期 datetime,单价 money)
insert into 库存表 values('001' , 10 , '2008-03-01',10)
insert into 库存表 values('001' , 16 , '2008-03-02',9.8)
insert into 库存表 values('001' , 29 , '2008-03-03',11.5)
insert into 库存表 values('002' , 12 , '2008-03-08',5)
insert into 库存表 values('002' , 15 , '2008-03-09',5.2)
create table 销售表(编码 varchar(10), 销售数量 int)
insert into 销售表 values('001' , 15 )
insert into 销售表 values('002' , 20 )
go