22,298
社区成员
发帖
与我相关
我的任务
分享高分求复杂结存SQL算法!
有6个数据表:
MaterialBase, --物料基础表
MaterialStock, --库存表
MaterialIOMaster, --业务主表
MaterialIODetail, --业务明细表
MaterialTransferMaster, --物料调拨主表
MaterialTransferDetail --物料调拨明细表
下面为各数据表结构...
MaterialBase:
MType --物料类型(FK)
MaterialID --物料编码(PK)
VirtualQty --帐面库存量
Unit --计量单位(FK)
WasteRate --报废率
RoHS --环保
---------------------------------------------
MaterialStock:
AutoID --自动编号(PK)
MaterialID --物料编码(FK) 跟MaterialBase.MaterialID 相关联
SuppilerID --供应商编号(FK)
SuppilerOrder --供应商单号(FK)
Depot --存放仓库(FK)
StockQty --库存量
BalanceQty --结存量 每次物料盘点后更新
---------------------------------------------
MaterialIOMaster:
ID --单号(PK)
Date --日期
Depot --仓库(FK)
Tag --库存业务类型:0=入库;1=发料;2=退料;3=退货;4=报损
---------------------------------------------
MaterialIODetail:
AutoID --自动编号(PK)
ID --单号(FK) 跟MaterialIOMaster.ID 相关联
Tag --库存业务类型
MaterialID --物料编码(FK)
SuppilerID --供应商编号(FK)
SuppilerOrder --供应商单号(FK)
Qty --业务数量
---------------------------------------------
MaterialTransferMaster:
ID --单号(PK)
Date --日期
DepotOut --调出仓库(FK)
DepotIn --调入仓库(FK)
---------------------------------------------
MaterialIODetail:
AutoID --自动编号(PK)
ID --单号(FK) 跟MaterialTransferMaster.ID 相关联
MaterialID --物料编码(FK)
SuppilerID --供应商编号(FK)
SuppilerOrder --供应商单号(FK)
Qty --调拨数量
---------------------------------------------
---------------------------------------------
跟据两个日期(StartDate,EndDate),统计出类似下面的数据结果:
ID Date MaterialID SuppilerID SuppilerOrder Depot 初存 入库 发料 退料 退货 报废 调拨 结存 Unit
T1 2009-2-1 M001 S001 SO001 D01 100 20 0 0 0 0 0 120 PCS
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T2 2009-2-2 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
T2 2009-2-2 M002 S002 SO002 D01 1000 200 100 0 0 0 0 1100 PCS
......
MaterialBase, --物料基础表
MaterialStock, --库存表
MaterialIOMaster, --业务主表
MaterialIODetail, --业务明细表
MaterialTransferMaster, --物料调拨主表
MaterialTransferDetail --物料调拨明细表
下面为各数据表结构...
MaterialBase:
MType --物料类型(FK)
MaterialID --物料编码(PK)
VirtualQty --帐面库存量
Unit --计量单位(FK)
WasteRate --报废率
RoHS --环保
---------------------------------------------
MaterialStock:
AutoID --自动编号(PK)
MaterialID --物料编码(FK) 跟MaterialBase.MaterialID 相关联
SuppilerID --供应商编号(FK)
SuppilerOrder --供应商单号(FK)
Depot --存放仓库(FK)
StockQty --库存量
BalanceQty --结存量 每次物料盘点后更新
---------------------------------------------
MaterialIOMaster:
ID --单号(PK)
Date --日期
Depot --仓库(FK)
Tag --库存业务类型:0=入库;1=发料;2=退料;3=退货;4=报损
---------------------------------------------
MaterialIODetail:
AutoID --自动编号(PK)
ID --单号(FK) 跟MaterialIOMaster.ID 相关联
Tag --库存业务类型
MaterialID --物料编码(FK)
SuppilerID --供应商编号(FK)
SuppilerOrder --供应商单号(FK)
Qty --业务数量
---------------------------------------------
MaterialTransferMaster:
ID --单号(PK)
Date --日期
DepotOut --调出仓库(FK)
DepotIn --调入仓库(FK)
---------------------------------------------
MaterialIODetail:
AutoID --自动编号(PK)
ID --单号(FK) 跟MaterialTransferMaster.ID 相关联
MaterialID --物料编码(FK)
SuppilerID --供应商编号(FK)
SuppilerOrder --供应商单号(FK)
Qty --调拨数量
---------------------------------------------
---------------------------------------------
跟据两个日期(StartDate,EndDate),统计出类似下面的数据结果:
ID Date MaterialID SuppilerID SuppilerOrder Depot 初存 入库 发料 退料 退货 报废 调拨 结存 Unit
T1 2009-2-1 M001 S001 SO001 D01 100 20 0 0 0 0 0 120 PCS
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T2 2009-2-2 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
T2 2009-2-2 M002 S002 SO002 D01 1000 200 100 0 0 0 0 1100 PCS
......
...全文
请发表友善的回复…
发表回复
3tzjq 2009-02-23
- 打赏
- 举报
顶!
谁解决上楼的问题,100分全属他(她).
谁解决上楼的问题,100分全属他(她).
fcuandy 2009-02-23
- 打赏
- 举报
没有交待业务逻辑,就是说这六张表，你的结果是按什么规则来的
zzxap 2009-02-21
- 打赏
- 举报
[code=SQL]
大概写下
select a.你要的字段,b.你要的字段 from MaterialIOMaster a inner join MaterialTransferMaster b
on a.id=b.id inner join 其它表 d on a.id=d.id ..... where a.date between 'StartDate' and 'EndDate'
[/CODE]
大概写下
select a.你要的字段,b.你要的字段 from MaterialIOMaster a inner join MaterialTransferMaster b
on a.id=b.id inner join 其它表 d on a.id=d.id ..... where a.date between 'StartDate' and 'EndDate'
[/CODE]
ws_hgo 2009-02-21
- 打赏
- 举报
好长
先看下
先看下
you_tube 2009-02-21
- 打赏
- 举报
最好贴点数据出来
you_tube 2009-02-21
- 打赏
- 举报
报废 调拨 结存
这三个状态都没有
这三个状态都没有
3tzjq 2009-02-21
- 打赏
- 举报
[Quote=引用 1 楼 htl258 的回复:]
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T2 2009-2-2 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
这两条差不多啊,那要怎么个汇总法?
[/Quote]
这个写错了,应该是
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T3 2009-2-3 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T2 2009-2-2 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
这两条差不多啊,那要怎么个汇总法?
[/Quote]
这个写错了,应该是
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T3 2009-2-3 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
jlj84237485 2009-02-21
- 打赏
- 举报
帮顶一下
htl258_Tony 2009-02-21
- 打赏
- 举报
不懂要根据什么来汇总你要的结果?
htl258_Tony 2009-02-21
- 打赏
- 举报
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T2 2009-2-2 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
这两条差不多啊,那要怎么个汇总法?
T2 2009-2-2 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
这两条差不多啊,那要怎么个汇总法?
3tzjq 2009-02-21
- 打赏
- 举报
--查询时间段定义
DECLARE @dt1 smalldatetime,@dt2 smalldatetime
SELECT @dt1='2005-1-1',@dt2='2005-3-10'
--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @dt smalldatetime
SELECT @dt=DATEADD(Day,1-Day(@dt1),@dt1)
--查询期初库存
SELECT
物料=ISNULL(a.物料,b.物料),
日期=ISNULL(b.日期,@dt1),
供应商=ISNULL(a.供应商,b.供应商),
供应商型号=ISNULL(a.供应商型号,b.供应商型号),
仓库=ISNULL(a.仓库,b.仓库),
初存=ISNULL(a.结存,0)+ISNULL(b.初存,0),
入库=ISNULL(b.[入库],0),
发料=ISNULL(b.[发料],0),
退料=ISNULL(b.[退料],0),
退货=ISNULL(b.[退货],0),
报废=ISNULL(b.[报废],0),
结存=ISNULL(a.结存,0)+ISNULL(b.初存,0)+ISNULL(b.末存,0)
FROM(SELECT 物料,供应商,供应商型号,仓库,结存 FROM Stocks)a --期初数
FULL JOIN(
SELECT
a.物料,
c.日期,
a.供应商,
a.供应商型号,
c.仓库,
初存=(SELECT SUM(CASE WHEN Flag=0 OR Flag=2 THEN tb.数量 ELSE -tb.数量 END)
FROM tb,tbm WHERE tbm.日期>=@dt AND tbm.日期<MIN(c.日期) AND tb.单号 = tbm.单号 AND tb.物料=a.物料 And tb.供应商 = a.供应商 And tb.供应商型号 = a.供应商型号),
入库=SUM(CASE WHEN Flag=0 THEN 数量 END),
发料=SUM(CASE WHEN Flag=1 THEN 数量 END),
退料=SUM(CASE WHEN Flag=2 THEN 数量 END),
退货=SUM(CASE WHEN Flag=3 THEN 数量 END),
报废=SUM(CASE WHEN Flag=4 THEN 数量 END),
末存=SUM(CASE WHEN Flag=0 OR Flag=2 THEN 数量 ELSE -数量 END)
FROM tb a,tbm c
WHERE c.日期>=@dt1 AND c.日期<DATEADD(Day,1,@dt2) And c.单号 = a.单号
GROUP BY c.日期,物料,供应商,供应商型号,c.仓库
)b ON a.物料=b.物料 And a.供应商 = b.供应商 And a.供应商型号 = b.供应商型号
ORDER BY 物料,供应商,供应商型号3tzjq 2009-02-21
- 打赏
- 举报
调了半天,就快要实际了!可还是不能区分仓库(MaterialIOMaster.Depot = MaterialStock.Depot).导致日期错误.请大家帮忙改改,还有怎么加入调拨单的明细账到此统计中?
下面是测试SQL:
下面是测试SQL:
USE CIMS_Debug
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'MaterialBase')
DROP Table MaterialBase
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stocks')
DROP Table Stocks
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tbm')
DROP Table tbm
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tb')
DROP Table tb
GO
CREATE TABLE MaterialBase(物料 varchar(10),描述 varchar(255))
INSERT MaterialBase SELECT 'aa','Test'
UNION ALL SELECT 'bb','0805,10K,+/-5%'
UNION ALL SELECT 'cc',NULL
CREATE TABLE Stocks(物料 varchar(10),供应商 varchar(15),供应商型号 varchar(50),仓库 varchar(10),结存 int)
INSERT Stocks SELECT 'aa','S01','S001','d01',100
INSERT Stocks SELECT 'aa','S01','S001','d02',50
UNION ALL SELECT 'bb','S02','S0021','d01',80
UNION ALL SELECT 'cc','S02','S0021','d01',50
--明细账数据
CREATE TABLE tbm(单号 varchar(15)PRIMARY KEY,仓库 varchar(10),日期 datetime)
INSERT tbm SELECT 'D01','d01','2005-1-1'
UNION ALL SELECT 'D02','d02','2005-2-2'
UNION ALL SELECT 'D03','d01','2005-2-7'
UNION ALL SELECT 'D04','d01','2005-1-8'
UNION ALL SELECT 'D05','d01','2005-3-8'
UNION ALL SELECT 'D06','d01','2005-2-5'
--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
单号 varchar(15), --单号
物料 varchar(10), --产品编号
供应商 varchar(15),
供应商型号 varchar(50),
数量 int, --交易数量
Flag tinyint) --交易标志,0代表入库,1代表发料,2代表退料,3代表退货,4代表报废,这样可以有效区分退货(-)
INSERT tb SELECT 'D01','aa','S01','S001',50,0
UNION ALL SELECT 'D01','aa','S01','S001',90,0
UNION ALL SELECT 'D01','aa','S01','S001',55 ,1
UNION ALL SELECT 'D02','aa','S01','S001',10,2
UNION ALL SELECT 'D02','aa','S01','S001',5 ,4
UNION ALL SELECT 'D02','aa','S01','S001',200,0
UNION ALL SELECT 'D02','aa','S01','S001',90 ,3
UNION ALL SELECT 'D02','bb','S02','S0021',95 ,0
UNION ALL SELECT 'D03','bb','S02','S0021',65 ,1
UNION ALL SELECT 'D03','bb','S02','S0021',15,0
UNION ALL SELECT 'D03','bb','S02','S0021',20,3
UNION ALL SELECT 'D03','bb','S02','S0021',10,0
UNION ALL SELECT 'D04','cc','S02','S0021',40,0
UNION ALL SELECT 'D05','cc','S02','S0021',25,1
UNION ALL SELECT 'D05','cc','S02','S0021',25,3
UNION ALL SELECT 'D06','cc','S02','S0021',5,2
UNION ALL SELECT 'D06','cc','S02','S0021',3,4
--查询时间段定义
DECLARE @dt1 smalldatetime,@dt2 smalldatetime
SELECT @dt1='2005-1-1',@dt2='2005-3-10'
--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @dt smalldatetime
SELECT @dt=DATEADD(Day,1-Day(@dt1),@dt1)
--查询期初库存
SELECT
物料=ISNULL(a.物料,b.物料),
日期=ISNULL(b.日期,@dt1),
供应商=ISNULL(a.供应商,b.供应商),
供应商型号=ISNULL(a.供应商型号,b.供应商型号),
仓库=ISNULL(a.仓库,b.仓库),
初存=ISNULL(a.结存,0)+ISNULL(b.初存,0),
入库=ISNULL(b.[入库],0),
发料=ISNULL(b.[发料],0),
退料=ISNULL(b.[退料],0),
退货=ISNULL(b.[退货],0),
报废=ISNULL(b.[报废],0),
结存=ISNULL(a.结存,0)+ISNULL(b.初存,0)+ISNULL(b.末存,0)
FROM(SELECT 物料,供应商,供应商型号,仓库,结存 FROM Stocks)a --期初数
FULL JOIN(
SELECT
a.物料,
c.日期,
a.供应商,
a.供应商型号,
c.仓库,
初存=(SELECT SUM(CASE WHEN Flag=0 OR Flag=2 THEN tb.数量 ELSE -tb.数量 END)
FROM tb,tbm WHERE tbm.日期>=@dt AND tbm.日期<MIN(c.日期) AND tb.单号 = tbm.单号 AND tb.物料=a.物料 And tb.供应商 = a.供应商 And tb.供应商型号 = a.供应商型号),
入库=SUM(CASE WHEN Flag=0 THEN 数量 END),
发料=SUM(CASE WHEN Flag=1 THEN 数量 END),
退料=SUM(CASE WHEN Flag=2 THEN 数量 END),
退货=SUM(CASE WHEN Flag=3 THEN 数量 END),
报废=SUM(CASE WHEN Flag=4 THEN 数量 END),
末存=SUM(CASE WHEN Flag=0 OR Flag=2 THEN 数量 ELSE -数量 END)
FROM tb a,tbm c
WHERE c.日期>=@dt1 AND c.日期<DATEADD(Day,1,@dt2) And c.单号 = a.单号
GROUP BY c.日期,物料,供应商,供应商型号,c.仓库
)b ON a.物料=b.物料 And a.供应商 = b.供应商 And a.供应商型号 = b.供应商型号
ORDER BY 物料,供应商,供应商型号
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'MaterialBase')
DROP Table MaterialBase
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'Stocks')
DROP Table Stocks
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tbm')
DROP Table tbm
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'tb')
DROP Table tb
GO
悔说话的哑巴 2009-02-21
- 打赏
- 举报
select a.你要的字段,b.你要的字段 from MaterialIOMaster a inner join MaterialTransferMaster b
on a.id=b.id inner join 其它表 d on a.id=d.id ..... where a.date between 'StartDate' and 'EndDate'
on a.id=b.id inner join 其它表 d on a.id=d.id ..... where a.date between 'StartDate' and 'EndDate'
htl258_Tony 2009-02-21
- 打赏
- 举报
最好的办法,每个表放四五条典型的数据,最好你能把表create出来,数据insert进去,想要的结果列出来,这样解决起来才快.
htl258_Tony 2009-02-21
- 打赏
- 举报
[Quote=引用 4 楼 3tzjq 的回复:]
引用 1 楼 htl258 的回复:
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T2 2009-2-2 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
这两条差不多啊,那要怎么个汇总法?
这个写错了,应该是
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS…
[/Quote]
只是ID不同,其它相同,这算统计吗,如果一单一单,就是明细表了.
引用 1 楼 htl258 的回复:
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS
T2 2009-2-2 M001 S001 SO001 D01 115 0 0 0 50 0 10 55 PCS
这两条差不多啊,那要怎么个汇总法?
这个写错了,应该是
T2 2009-2-2 M001 S001 SO001 D01 120 0 10 5 0 0 0 115 PCS…
[/Quote]
只是ID不同,其它相同,这算统计吗,如果一单一单,就是明细表了.
3tzjq 2009-02-21
- 打赏
- 举报
大家帮忙啊!
