22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#入库表') is null
drop table #入库表
Go
Create table #入库表([编号] nvarchar(23),[入库日期] Date,[入库数量] int,[出库数量] int)
Insert #入库表
select N'001','2017-7-1',100,0 union all
select N'001','2017-7-5',200,0 union all
select N'001','2017-7-11',200,0 union all
select N'002','2017-7-2',200,0 union all
select N'002','2017-7-10',300,0
GO
if not object_id(N'Tempdb..#出库表') is null
drop table #出库表
Go
Create table #出库表([编号] nvarchar(23),[出库日期] Date,[出库数量] int)
Insert #出库表
select N'001','2017-7-12',200 union all
select N'001','2017-7-13',200 union all
select N'002','2017-7-13',100 union all
select N'002','2017-7-15',300
Go
--测试数据结束
;WITH ctea AS (
Select *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [入库日期]) num1 from #入库表
),cteb AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [出库日期]) num2 FROM #出库表
),cte AS (
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量,
ctea.num1,
cteb.num2
FROM ctea
LEFT JOIN cteb ON cteb.编号 = ctea.编号
WHERE num1 = 1
AND num2 = 1
UNION ALL
SELECT cte.[编号] ,
cte.[入库日期] ,
cte.[入库数量] ,
cteb.出库日期,
CASE WHEN cte.剩余数量<= cteb.出库数量 THEN cte.剩余数量
ELSE cteb.出库数量
END AS 出库数量 ,
cte.剩余数量 - cteb.出库数量 AS 剩余数量,
cte.num1,
cteb.num2
FROM cte
JOIN cteb
ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 > 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cte.出库日期,
CASE WHEN ABS(cte.剩余数量)>= ctea.入库数量 THEN 0
ELSE ABS(cte.剩余数量)
END AS 出库数量 ,
cte.剩余数量 + ctea.入库数量 AS 剩余数量,
ctea.num1,
cte.num2
FROM cte
JOIN ctea
ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 +1
WHERE cte.剩余数量 < 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期 ,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量 ,
ctea.num1 ,
cteb.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
JOIN cteb ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 = 0
UNION ALL
SELECT ctea.编号 ,
ctea.[入库日期] ,
ctea.[入库数量] ,
'' AS 出库日期,
0 AS 出库数量 ,
ctea.入库数量 AS 剩余数量 ,
ctea.num1 ,
cte.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
WHERE cte.剩余数量 >= 0
AND NOT EXISTS ( SELECT *
FROM cteb
WHERE cte.编号 = cteb.编号
AND cteb.num2 > cte.num2 )
)
--更新操作
UPDATE a
SET 出库数量 = t.出库数量
FROM #入库表 a
JOIN ( SELECT 编号 ,
入库日期 ,
入库数量 ,
SUM(出库数量) AS 出库数量
FROM cte
GROUP BY 编号 ,
入库日期 ,
入库数量
) t ON t.编号 = a.编号
AND t.入库日期 = a.入库日期
AND t.入库数量 = a.入库数量
--读取测试结果
SELECT * FROM #入库表
[/quote]
非常感谢,--测试数据
if not object_id(N'Tempdb..#入库表') is null
drop table #入库表
Go
Create table #入库表([编号] nvarchar(23),[入库日期] Date,[入库数量] int,[出库数量] int)
Insert #入库表
select N'001','2017-7-1',100,0 union all
select N'001','2017-7-5',200,0 union all
select N'001','2017-7-11',200,0 union all
select N'002','2017-7-2',200,0 union all
select N'002','2017-7-10',300,0
GO
if not object_id(N'Tempdb..#出库表') is null
drop table #出库表
Go
Create table #出库表([编号] nvarchar(23),[出库日期] Date,[出库数量] int)
Insert #出库表
select N'001','2017-7-12',200 union all
select N'001','2017-7-13',200 union all
select N'002','2017-7-13',100 union all
select N'002','2017-7-15',300
Go
--测试数据结束
;WITH ctea AS (
Select *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [入库日期]) num1 from #入库表
),cteb AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [出库日期]) num2 FROM #出库表
),cte AS (
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量,
ctea.num1,
cteb.num2
FROM ctea
LEFT JOIN cteb ON cteb.编号 = ctea.编号
WHERE num1 = 1
AND num2 = 1
UNION ALL
SELECT cte.[编号] ,
cte.[入库日期] ,
cte.[入库数量] ,
cteb.出库日期,
CASE WHEN cte.剩余数量<= cteb.出库数量 THEN cte.剩余数量
ELSE cteb.出库数量
END AS 出库数量 ,
cte.剩余数量 - cteb.出库数量 AS 剩余数量,
cte.num1,
cteb.num2
FROM cte
JOIN cteb
ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 > 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cte.出库日期,
CASE WHEN ABS(cte.剩余数量)>= ctea.入库数量 THEN 0
ELSE ABS(cte.剩余数量)
END AS 出库数量 ,
cte.剩余数量 + ctea.入库数量 AS 剩余数量,
ctea.num1,
cte.num2
FROM cte
JOIN ctea
ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 +1
WHERE cte.剩余数量 < 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期 ,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量 ,
ctea.num1 ,
cteb.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
JOIN cteb ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 = 0
UNION ALL
SELECT ctea.编号 ,
ctea.[入库日期] ,
ctea.[入库数量] ,
'' AS 出库日期,
0 AS 出库数量 ,
ctea.入库数量 AS 剩余数量 ,
ctea.num1 ,
cte.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
WHERE cte.剩余数量 >= 0
AND NOT EXISTS ( SELECT *
FROM cteb
WHERE cte.编号 = cteb.编号
AND cteb.num2 > cte.num2 )
)
--更新操作
UPDATE a
SET 出库数量 = t.出库数量
FROM #入库表 a
JOIN ( SELECT 编号 ,
入库日期 ,
入库数量 ,
SUM(出库数量) AS 出库数量
FROM cte
GROUP BY 编号 ,
入库日期 ,
入库数量
) t ON t.编号 = a.编号
AND t.入库日期 = a.入库日期
AND t.入库数量 = a.入库数量
--读取测试结果
SELECT * FROM #入库表
--测试数据
if not object_id(N'Tempdb..#入库表') is null
drop table #入库表
Go
Create table #入库表([编号] nvarchar(23),[入库日期] Date,[入库数量] int,[出库数量] int)
Insert #入库表
select N'001','2017-7-1',100,0 union all
select N'001','2017-7-5',200,0 union all
select N'001','2017-7-11',200,0 union all
select N'002','2017-7-2',200,0 union all
select N'002','2017-7-10',300,0
GO
if not object_id(N'Tempdb..#出库表') is null
drop table #出库表
Go
Create table #出库表([编号] nvarchar(23),[出库日期] Date,[出库数量] int)
Insert #出库表
select N'001','2017-7-12',200 union all
select N'001','2017-7-13',200 union all
select N'002','2017-7-13',100 union all
select N'002','2017-7-15',300
Go
--测试数据结束
;WITH ctea AS (
Select *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [入库日期]) num1 from #入库表
),cteb AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [出库日期]) num2 FROM #出库表
),cte AS (
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量,
ctea.num1,
cteb.num2
FROM ctea
LEFT JOIN cteb ON cteb.编号 = ctea.编号
WHERE num1 = 1
AND num2 = 1
UNION ALL
SELECT cte.[编号] ,
cte.[入库日期] ,
cte.[入库数量] ,
cteb.出库日期,
CASE WHEN cte.剩余数量<= cteb.出库数量 THEN cte.剩余数量
ELSE cteb.出库数量
END AS 出库数量 ,
cte.剩余数量 - cteb.出库数量 AS 剩余数量,
cte.num1,
cteb.num2
FROM cte
JOIN cteb
ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 > 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cte.出库日期,
CASE WHEN ABS(cte.剩余数量)>= ctea.入库数量 THEN 0
ELSE ABS(cte.剩余数量)
END AS 出库数量 ,
cte.剩余数量 + ctea.入库数量 AS 剩余数量,
ctea.num1,
cte.num2
FROM cte
JOIN ctea
ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 +1
WHERE cte.剩余数量 < 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期 ,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量 ,
ctea.num1 ,
cteb.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
JOIN cteb ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 = 0
UNION ALL
SELECT ctea.编号 ,
ctea.[入库日期] ,
ctea.[入库数量] ,
'' AS 出库日期,
0 AS 出库数量 ,
ctea.入库数量 AS 剩余数量 ,
ctea.num1 ,
cte.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
WHERE cte.剩余数量 >= 0
AND NOT EXISTS ( SELECT *
FROM cteb
WHERE cte.编号 = cteb.编号
AND cteb.num2 > cte.num2 )
)
SELECT 编号 ,
入库日期 ,
入库数量 ,
SUM(出库数量) AS 出库数量
FROM cte
GROUP BY 编号 ,
入库日期 ,
入库数量
ORDER BY 编号 ,
入库日期
[/quote]
你好,数据处理出来是这样,怎么样直接update到入库表中呢?谢谢--测试数据
if not object_id(N'Tempdb..#入库表') is null
drop table #入库表
Go
Create table #入库表([编号] nvarchar(23),[入库日期] Date,[入库数量] int,[出库数量] int)
Insert #入库表
select N'001','2017-7-1',100,0 union all
select N'001','2017-7-5',200,0 union all
select N'001','2017-7-11',200,0 union all
select N'002','2017-7-2',200,0 union all
select N'002','2017-7-10',300,0
GO
if not object_id(N'Tempdb..#出库表') is null
drop table #出库表
Go
Create table #出库表([编号] nvarchar(23),[出库日期] Date,[出库数量] int)
Insert #出库表
select N'001','2017-7-12',200 union all
select N'001','2017-7-13',200 union all
select N'002','2017-7-13',100 union all
select N'002','2017-7-15',300
Go
--测试数据结束
;WITH ctea AS (
Select *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [入库日期]) num1 from #入库表
),cteb AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [出库日期]) num2 FROM #出库表
),cte AS (
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量,
ctea.num1,
cteb.num2
FROM ctea
LEFT JOIN cteb ON cteb.编号 = ctea.编号
WHERE num1 = 1
AND num2 = 1
UNION ALL
SELECT cte.[编号] ,
cte.[入库日期] ,
cte.[入库数量] ,
cteb.出库日期,
CASE WHEN cte.剩余数量<= cteb.出库数量 THEN cte.剩余数量
ELSE cteb.出库数量
END AS 出库数量 ,
cte.剩余数量 - cteb.出库数量 AS 剩余数量,
cte.num1,
cteb.num2
FROM cte
JOIN cteb
ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 > 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cte.出库日期,
CASE WHEN ABS(cte.剩余数量)>= ctea.入库数量 THEN 0
ELSE ABS(cte.剩余数量)
END AS 出库数量 ,
cte.剩余数量 + ctea.入库数量 AS 剩余数量,
ctea.num1,
cte.num2
FROM cte
JOIN ctea
ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 +1
WHERE cte.剩余数量 < 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期 ,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量 ,
ctea.num1 ,
cteb.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
JOIN cteb ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 = 0
UNION ALL
SELECT ctea.编号 ,
ctea.[入库日期] ,
ctea.[入库数量] ,
'' AS 出库日期,
0 AS 出库数量 ,
ctea.入库数量 AS 剩余数量 ,
ctea.num1 ,
cte.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
WHERE cte.剩余数量 >= 0
AND NOT EXISTS ( SELECT *
FROM cteb
WHERE cte.编号 = cteb.编号
AND cteb.num2 > cte.num2 )
)
SELECT 编号 ,
入库日期 ,
入库数量 ,
SUM(出库数量) AS 出库数量
FROM cte
GROUP BY 编号 ,
入库日期 ,
入库数量
ORDER BY 编号 ,
入库日期
--测试数据
if not object_id(N'Tempdb..#入库表') is null
drop table #入库表
Go
Create table #入库表([编号] nvarchar(23),[入库日期] Date,[入库数量] int,[出库数量] int)
Insert #入库表
select N'001','2017-7-1',100,0 union all
select N'001','2017-7-5',200,0 union all
select N'001','2017-7-11',200,0 union all
select N'002','2017-7-2',200,0 union all
select N'002','2017-7-10',300,0
GO
if not object_id(N'Tempdb..#出库表') is null
drop table #出库表
Go
Create table #出库表([编号] nvarchar(23),[出库日期] Date,[出库数量] int)
Insert #出库表
select N'001','2017-7-12',200 union all
select N'001','2017-7-13',200 union all
select N'002','2017-7-13',100 union all
select N'002','2017-7-15',300
Go
--测试数据结束
;WITH ctea AS (
Select *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [入库日期]) num1 from #入库表
),cteb AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY [编号] ORDER BY [出库日期]) num2 FROM #出库表
),cte AS (
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量,
ctea.num1,
cteb.num2
FROM ctea
LEFT JOIN cteb ON cteb.编号 = ctea.编号
WHERE num1 = 1
AND num2 = 1
UNION ALL
SELECT cte.[编号] ,
cte.[入库日期] ,
cte.[入库数量] ,
cteb.出库日期,
CASE WHEN cte.剩余数量<= cteb.出库数量 THEN cte.剩余数量
ELSE cteb.出库数量
END AS 出库数量 ,
cte.剩余数量 - cteb.出库数量 AS 剩余数量,
cte.num1,
cteb.num2
FROM cte
JOIN cteb
ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 > 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cte.出库日期,
CASE WHEN ABS(cte.剩余数量)>= ctea.入库数量 THEN 0
ELSE ABS(cte.剩余数量)
END AS 出库数量 ,
cte.剩余数量 + ctea.入库数量 AS 剩余数量,
ctea.num1,
cte.num2
FROM cte
JOIN ctea
ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 +1
WHERE cte.剩余数量 < 0
UNION ALL
SELECT ctea.[编号] ,
ctea.[入库日期] ,
ctea.[入库数量] ,
cteb.出库日期 ,
CASE WHEN cteb.出库数量 >= ctea.入库数量 THEN ctea.入库数量
ELSE cteb.出库数量
END AS 出库数量 ,
ctea.入库数量 - cteb.出库数量 AS 剩余数量 ,
ctea.num1 ,
cteb.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
JOIN cteb ON cte.编号 = cteb.编号
AND cteb.num2 = cte.num2 + 1
WHERE cte.剩余数量 = 0
UNION ALL
SELECT ctea.编号 ,
ctea.[入库日期] ,
ctea.[入库数量] ,
'' AS 出库日期,
0 AS 出库数量 ,
ctea.入库数量 AS 剩余数量 ,
ctea.num1 ,
cte.num2
FROM cte
JOIN ctea ON cte.编号 = ctea.编号
AND ctea.num1 = cte.num1 + 1
WHERE cte.剩余数量 >= 0
AND NOT EXISTS ( SELECT *
FROM cteb
WHERE cte.编号 = cteb.编号
AND cteb.num2 > cte.num2 )
)
SELECT 编号 ,
入库日期 ,
入库数量 ,
出库日期 ,
出库数量 ,
剩余数量
FROM cte
ORDER BY 编号 ,
入库日期