请教材料采购计划表的SQL写法？
create table t_kc --库存表
(
type varchar(20),--材料类别
mc varchar(20),--材料名称
dw varchar(20),--材料单位
gg varchar(20),--材料规格
sl int, --出入库数量
dj decimal(13,2),--单价
status char(1),--出入库状态 1为入库 0为出库
bz varchar(50),--材料用途
gydw varchar(50), --供应单位
rq datetime --出入库日期
)
insert into t_kc select '标准件 ', '螺丝 ', '根 ', 'M10 ',100,1, '1 ',null, '供应部门1 ', '2007-6-1 '
insert into t_kc select '标准件 ', '螺丝 ', '根 ', 'M10 ',100,1.5, '1 ',null, '供应部门2 ', '2007-6-2 '
insert into t_kc select '标准件 ', '螺丝 ', '根 ', 'M10 ',30,1, '0 ', '车底板 ',null, '2007-6-3 '
create table t_bzj_zl_ys --材料预算表
(
type varchar(20), --材料类别
name varchar(20), --材料名称
dw varchar(20), --材料单位
gg varchar(20), --材料规格
sl int, --使用数量
dj decimal(13,2), --材料单价
yt varchar(50)--用途
)
insert into t_bzj_zl_ys select '标准件 ', '螺丝 ', '根 ', 'M10 ',200,2, '车底板 '
/*
要得到材料采购计划表，按照材料类别，名称，单位，规格进行分组，格式为：
材料类别 材料名称 单位 规格 用途 计划用料数量 采购数量 最后一次入库单价 金额 最后一次供应单位 库存结余数量
标准件 螺丝 根 M10 车底板 200 30 1.5 45 供应部门2 170
*/
drop table t_bzj_zl_ys
drop table t_kc
[解决办法]
select
a.type,
a.mc,
a.dw,
a.gg,
a.yt,
a.sl as 计划用料数量,
case when a.sl> isnull(t.sl,0) then a.sl-isnull(t.sl,0) else 0 end as 采购数量,
t.dj as 最后一次入库单价,
case when a.sl> isnull(t.sl,0) then a.sl-isnull(t.sl,0) else 0 end*isnull(t.dj,0) as 金额,
t.gydw as 最后一次供应单位,
t.sl as 库存结余数量
from t_bzj_zl_ys a left join (
select
type,mc,dw,gg,
sum(case when status=1 then sl else -sl end) as sl,
(select top 1 dj from t_kc where type=b.type and mc=t.mc and dw=b.dw and gg=b.gg and status= '1 ' order by rq desc) as sj,
(select top 1 gydw from t_kc where type=b.type and mc=t.mc and dw=b.dw and gg=b.gg and status= '1 ' order by rq desc) as gydw
from t_kc b
group by type,mc,dw,gg
) as t
on a.type=t.type
and a.mc=t.mc
and a.dw=t.dw
and a.gg=t.gg
[解决办法]
SELECT A.type AS 材料类别,A.[name] AS 材料名称,A.dw AS 单位,A.gg AS 规格,A.yt AS 用途,A.sl AS 计划用料数量,B.dj AS 最后一次入库单价 ,A.sl-C.sl 计划用料数量, (A.sl-C.sl)*B.dj AS 金额,B.gydw AS 最后一次供应单位
,C.sl 库存结余数量
FROM t_bzj_zl_ys AS A
LEFT OUTER JOIN --最后入库信息
(SELECT * FROM t_kc AS A1 WHERE status=1 AND NOT EXISTS(SELECT 1 FROM t_kc AS B1 WHERE B1.status=1 AND B1.type=A1.type AND B1.mc=A1.mc AND B1.rq> A1.rq))
AS B ON B.type=A.type AND A.[name]=B.mc
LEFT OUTER JOIN --统计进出库数量
(SELECT type,mc,SUM(CASE status WHEN 1 THEN sl ELSE -sl END) AS sl FROM t_kc GROUP BY type,mc)
AS C ON C.type=A.type AND A.[name]=C.mc
/*
结果：
材料类别 材料名称 单位 规格 用途 计划用料数量 采购数量 最后一次入库单价 金额 最后一次供应单位 库存结余数量
标准件 螺丝 根 M10 车底板 200 30 1.5 45 供应部门2 170
*/
[解决办法]
select a.*, sum(case when b.status=0 then b.sl else 0 end),c.dj,sum(case when b.status=0 then b.sl else 0 end)*c.dj,c.gydw,
sum(case when b.status=0 then -b.sl else b.sl end)
from t_bzj_zl_ys a inner join t_kc b on a.type=b.type and a.name=b.mc and a.dw=b.dw and a.gg=b.gg
inner join (
select * from t_kc a where
not exists (select 1 from t_kc where a.type=type and a.mc=mc and a.dw=dw and a.gg=gg and a.rq <rq and status <> 0) and status <> 0 )c
on a.type=c.type and a.name=c.mc and c.dw=b.dw and c.gg=b.gg
group by a.type, a.name, a.dw, a.gg, a.sl, a.dj, a.yt ,c.gydw,c.dj
我理解的
[解决办法]
select
a.type,
t.mc,
a.dw,
a.gg,
a.yt,
a.sl as 计划用料数量,
case when a.sl> isnull(t.sl,0) then a.sl-isnull(t.sl,0) else 0 end as 采购数量,
t.dj as 最后一次入库单价,
case when a.sl> isnull(t.sl,0) then a.sl-isnull(t.sl,0) else 0 end*isnull(t.dj,0) as 金额,
t.gydw as 最后一次供应单位,
t.sl as 库存结余数量
from t_bzj_zl_ys a left join (
select
type,mc,dw,gg,
sum(case when status=1 then sl else -sl end) as sl,
(select top 1 dj from t_kc where type=b.type and mc=b.mc and dw=b.dw and gg=b.gg and status= '1 ' order by rq desc) as dj,
(select top 1 gydw from t_kc where type=b.type and mc=b.mc and dw=b.dw and gg=b.gg and status= '1 ' order by rq desc) as gydw
from t_kc b
group by type,mc,dw,gg
) as t
on a.type=t.type
and a.dw=t.dw
and a.gg=t.gg
==========================
type mc dw gg yt 计划用料数量 采购数量 后一次入库单价 金额 最后一次供应单位 库存结余数量
-------- -------------------- -------------------- -------------------- -------------------------------------------------- -
标准件 螺丝 根 M10 车底板 200 30 1.50 45.00 供应部门2 170
标准件 分泵 无 单 车底板 200 170 100.00 17000.00 供应部门1 30
(2 行受影响)
