DVD_01(OK_008 blog:wghao.cnblogs.com)请进
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 '
insert into t_kc select '标准件 ', '分泵 ', '无 ', '单 ',10,100, '1 ',null, '供应部门2 ', '2007-6-2 '
insert into t_kc select '标准件 ', '分泵 ', '无 ', '单 ',10,100, '1 ',null, '供应部门1 ', '2007-6-2 '
insert into t_kc select '标准件 ', '分泵 ', '无 ', '单 ',10,100, '1 ',null, '供应部门1 ', '2007-6-2 '
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, '车底板 '
insert into t_bzj_zl_ys select '标准件 ', '分泵 ', '无 ', '单 ',200,100, '车底板 '
/*
要得到材料采购计划表，按照材料类别，名称，单位，规格进行分组，格式为：
材料类别 材料名称 单位 规格 用途 计划用料数量 采购数量 最后一次入库单价 金额 最后一次供应单位 库存结余数量
标准件 螺丝 根 M10 车底板 200 30 1.5 45 供应部门2 170
标准件
*/
SELECT IDENTITY(int,1,1) AS [id],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 库存结余数量
INTO #T --把结果些入临时表，为的是去除重复的记录（重复的原因：分泵 三个进货的日期相同导致重复记录）
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
--查询语句
SELECT * FROM #T AS A WHERE NOT EXISTS(SELECT 1 FROM #T AS B WHERE B.材料类别=A.材料类别 AND B.材料名称=A.材料名称 AND B.[id]> A.[ID])
DROP TABLE #T
drop table t_bzj_zl_ys
drop table t_kc
-------------------------------------------
请教一个小问题，怎样把“采购数量”小于等于0的记录不显示出来？
[解决办法]
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 '
insert into t_kc select '标准件 ', '分泵 ', '无 ', '单 ',10,100, '1 ',null, '供应部门2 ', '2007-6-2 '
insert into t_kc select '标准件 ', '分泵 ', '无 ', '单 ',10,100, '1 ',null, '供应部门1 ', '2007-6-2 '
insert into t_kc select '标准件 ', '分泵 ', '无 ', '单 ',10,100, '1 ',null, '供应部门1 ', '2007-6-2 '
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, '车底板 '
insert into t_bzj_zl_ys select '标准件 ', '分泵 ', '无 ', '单 ',200,100, '车底板 '
/*
要得到材料采购计划表，按照材料类别，名称，单位，规格进行分组，格式为：
材料类别 材料名称 单位 规格 用途 计划用料数量 采购数量 最后一次入库单价 金额 最后一次供应单位 库存结余数量
标准件 螺丝 根 M10 车底板 200 30 1.5 45 供应部门2 170
标准件
*/
SELECT IDENTITY(int,1,1) AS [id],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 库存结余数量
INTO #T --把结果些入临时表，为的是去除重复的记录（重复的原因：分泵 三个进货的日期相同导致重复记录）
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
Where A.sl-C.sl > 0 --------------加上條件
--查询语句
SELECT * FROM #T AS A WHERE NOT EXISTS(SELECT 1 FROM #T AS B WHERE B.材料类别=A.材料类别 AND B.材料名称=A.材料名称 AND B.[id]> A.[ID])
DROP TABLE #T
drop table t_bzj_zl_ys
drop table t_kc
[解决办法]
最後的查詢語句
SELECT * FROM #T AS A WHERE NOT EXISTS(SELECT 1 FROM #T AS B WHERE B.材料类别=A.材料类别 AND B.材料名称=A.材料名称 AND B.[id]> A.[ID]) and A.采购数量 > 0
