求一条SQL语句

lzw_0736 2017-02-08 04:51:51
表一
单据编号 物料编号 批号 数量
a01 a02 a31 100
a01 a02 a32 200

表二
单据编号 物料编号 拆分后单据编号 数量
a01 a02 a011 180
a01 a02 a012 120

表三
单据编号 物料编号 拆分后单据编号 批号 数量
a01 a02 a011 a31 100
a01 a02 a011 a32 80
a01 a02 a012 a32 120

请教大家，如何根据表一、表二得到表三？
...全文
519 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
lzw_0736 2017-03-24
  • 打赏
  • 举报
 回复
13楼roy_88老师的代码，经测试，完全没有问题，非常感谢！ 其他老师代码暂时未测试，有空时再测, 谢谢大家！
中国风 2017-02-22
  • 打赏
  • 举报
 回复
SQL 2005这样改效率高，可去测测，最好别用CTE一条条去递归 



use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([单据编号] nvarchar(31),[物料编号] nvarchar(38),[批号] nvarchar(31),[数量] int)
Insert #1
select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160218-06',67 union all
select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160394-06',56 union all
select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160407-05',34 union all
select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160416-07',48 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160218-07',23 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160394-07',56 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160407-06',34 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160410-01',5 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160416-08',48 union all
select N'KMR16004681',N'CTN2007131A-/2',N'SC160218-11',225 union all
select N'KMR16004681',N'CTN2007131A-/2',N'SC160394-10',100 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'SC160416-12',720 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'SC160548-05',480 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'SC160416-12',180 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'SC160548-05',120
Go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#2') is null
	drop table #2
Go
Create table #2([单据编号] nvarchar(31),[物料编号] nvarchar(38),[拆分后单据编号] nvarchar(33),[数量] int)
Insert #2
select N'KMR16004350',N'CTN2006164D-/36R1',N'KMR16004350',67 union all
select N'KMR16004350',N'CTN2006164D-/36R1',N'KMR16004350-1',138 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'KMR16004350',166 union all
select N'KMR16004681',N'CTN2007131A-/2',N'KMR16004681-1',225 union all
select N'KMR16004681',N'CTN2007131A-/2',N'KMR16004681-2',100 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465',228 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-1',272 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-2',220 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-3',480 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465',57 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-1',68 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-2',55 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-3',120
GO

;WITH a
AS
(SELECT *,(SELECT SUM([数量]) FROM #1 WHERE [物料编号]=a.[物料编号] AND ([单据编号]<a.[单据编号] OR [单据编号]=a.[单据编号] AND [批号]<=a.[批号]))  AS SumQty1 FROM  #1 AS a)
,b AS
(SELECT *,(SELECT SUM([数量]) FROM #2 WHERE [物料编号]=a.[物料编号] AND ([单据编号]<a.[单据编号] OR [单据编号]=a.[单据编号] AND [拆分后单据编号]<=a.[拆分后单据编号])) AS SumQty2 from #2 AS a)
SELECT  b.[单据编号],b.[物料编号],b.[拆分后单据编号],a.批号,CASE WHEN a.SumQty1>b.SumQty2 THEN b.SumQty2 ELSE a.SumQty1 END-CASE WHEN b.SumQty2 - b.数量>a.SumQty1 - a.数量 THEN b.SumQty2 - b.数量 ELSE a.SumQty1 - a.数量 END AS 扣减数量
FROM    a
        INNER JOIN b ON a.物料编号 = b.物料编号
                        AND a.SumQty1 > b.SumQty2 - b.数量
                        AND b.SumQty2 > a.SumQty1 - a.数量
ORDER BY 1;

/*
单据编号	物料编号	拆分后单据编号	批号	扣减数量
KMR16004350	CTN2006164D-/36R1	KMR16004350	SC160218-06	67
KMR16004350	CTN2006164D-/36R1	KMR16004350-1	SC160394-06	56
KMR16004350	CTN2006164D-/36R1	KMR16004350-1	SC160407-05	34
KMR16004350	CTN2006164D-/36R1	KMR16004350-1	SC160416-07	48
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160218-07	23
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160394-07	56
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160407-06	34
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160410-01	5
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160416-08	48
KMR16004681	CTN2007131A-/2	KMR16004681-1	SC160218-11	225
KMR16004681	CTN2007131A-/2	KMR16004681-2	SC160394-10	100
KMR16006465	CNR2006167A-/6R1	KMR16006465	SC160416-12	228
KMR16006465	CNR2006167A-/6R1	KMR16006465-1	SC160416-12	272
KMR16006465	CNR2006167A-/6R1	KMR16006465-2	SC160416-12	220
KMR16006465	CNR2006167A-/6R1	KMR16006465-3	SC160548-05	480
KMR16006465	CTN2006167A-6/24R3	KMR16006465	SC160416-12	57
KMR16006465	CTN2006167A-6/24R3	KMR16006465-1	SC160416-12	68
KMR16006465	CTN2006167A-6/24R3	KMR16006465-2	SC160416-12	55
KMR16006465	CTN2006167A-6/24R3	KMR16006465-3	SC160548-05	120
*/
Tiger_Zhao 2017-02-22
  • 打赏
  • 举报
 回复
把你的代码格式化了一下。
很明显的问题：上个批号正好分完的情况呢？
比如sub中 KMR16004350,67 这条，正好批号 SC160218-06,67 分完，sub中 KMR16004350-1,138 就不处理了。
我#3给你的例子中可是有3个UNION ALL啊。
;WITH cte_1 AS (

    SELECT 单据编号,

           物料编号,

           SUM(a.数量) AS 总数量

      FROM #mainorder a

  GROUP BY 单据编号,物料编号

),

cte_2 AS (

-- 开始

    SELECT a.单据编号,

           a.物料编号,

           a.批号,

           CASE WHEN b.数量 - a.数量 > 0

           THEN a.数量

           ELSE b.数量

           END AS 数量,

           b.拆分后单据编号,

           b.数量 - a.数量 AS 差值,

           c.总数量 - (CASE WHEN b.数量 - a.数量 > 0

                       THEN a.数量

                       ELSE b.数量

                       END) AS 剩余数量

      FROM #mainorder a

      JOIN #suborder b

        ON b.单据编号 = a.单据编号

       AND b.物料编号 = a.物料编号

       AND NOT EXISTS (SELECT 1 -- 第一条sub

                        FROM #suborder x

                       WHERE x.单据编号 = b.单据编号

                         AND x.物料编号 = b.物料编号

                         AND x.拆分后单据编号 < b.拆分后单据编号)

      JOIN cte_1 c

        ON c.单据编号 = a.单据编号

       AND c.物料编号 = a.物料编号

      WHERE NOT EXISTS (SELECT 1 -- 第一条main

                          FROM #mainorder x

                         WHERE x.单据编号 = a.单据编号

                           AND x.物料编号 = a.物料编号

                           AND x.批号 < a.批号)

UNION ALL -- 上个批号没分完（差值 > 0）

    SELECT a.单据编号,

           a.物料编号,

           b.批号,

           CASE WHEN b.数量 - a.数量 > 0

           THEN a.数量

           ELSE b.数量

           END AS 数量,

           a.拆分后单据编号,

           a.差值 - b.数量 AS 差值,

           a.剩余数量 - (CASE WHEN b.数量 - a.数量 > 0

                         THEN a.数量

                         ELSE b.数量

                         END) AS 剩余数量

      FROM cte_2 a

      JOIN #mainorder b

        ON b.单据编号 = a.单据编号

       AND b.物料编号 = a.物料编号

       AND b.批号 > a.批号

       AND NOT EXISTS (SELECT 1 -- 批号更大的main第一条

                         FROM dbo.#mainorder x

                        WHERE x.单据编号 = b.单据编号

                          AND x.物料编号 = b.物料编号

                          AND x.批号 > a.批号

                          AND x.批号 < b.批号)

     WHERE a.差值 > 0

       AND a.剩余数量 > 0

UNION ALL -- 上个批号不够分（差值 < 0）

    SELECT a.单据编号,

           a.物料编号,

           a.批号,

           CASE WHEN b.数量 + a.差值 > 0

           THEN Abs(a.差值)

           ELSE b.数量

           END AS 数量,

           b.拆分后单据编号,

           b.数量 + a.差值 AS 差值,

           a.剩余数量 - (CASE WHEN b.数量 + a.差值 > 0

                         THEN a.差值

                         ELSE b.数量

                         END) AS 剩余数量

      FROM cte_2 a

      JOIN #suborder b

        ON b.单据编号 = a.单据编号

       AND b.物料编号 = a.物料编号

       AND b.拆分后单据编号 > a.拆分后单据编号

       AND NOT EXISTS (SELECT 1 -- 拆分后单据编号更大的sub第一条

                         FROM dbo.#suborder x

                        WHERE x.单据编号 = b.单据编号

                          AND x.物料编号 = b.物料编号

                          AND x.拆分后单据编号 > a.拆分后单据编号

                          AND x.拆分后单据编号 < b.拆分后单据编号)

     WHERE a.差值 < 0

       AND a.剩余数量 > 0)

   SELECT a.单据编号,

          a.物料编号,

          a.拆分后单据编号,

          a.批号,

          a.数量

     FROM cte_2 a

 ORDER BY a.单据编号,

          a.物料编号,

          a.批号,

          a.拆分后单据编号
中国风 2017-02-21
  • 打赏
  • 举报
 回复
用#6拆分效果 e.g. 
 

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([单据编号] nvarchar(31),[物料编号] nvarchar(38),[批号] nvarchar(31),[数量] int)
Insert #1
select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160218-06',67 union all
select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160394-06',56 union all
select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160407-05',34 union all
select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160416-07',48 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160218-07',23 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160394-07',56 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160407-06',34 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160410-01',5 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'SC160416-08',48 union all
select N'KMR16004681',N'CTN2007131A-/2',N'SC160218-11',225 union all
select N'KMR16004681',N'CTN2007131A-/2',N'SC160394-10',100 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'SC160416-12',720 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'SC160548-05',480 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'SC160416-12',180 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'SC160548-05',120
Go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#2') is null
	drop table #2
Go
Create table #2([单据编号] nvarchar(31),[物料编号] nvarchar(38),[拆分后单据编号] nvarchar(33),[数量] int)
Insert #2
select N'KMR16004350',N'CTN2006164D-/36R1',N'KMR16004350',67 union all
select N'KMR16004350',N'CTN2006164D-/36R1',N'KMR16004350-1',138 union all
select N'KMR16004350',N'CTN2006165D-/36R',N'KMR16004350',166 union all
select N'KMR16004681',N'CTN2007131A-/2',N'KMR16004681-1',225 union all
select N'KMR16004681',N'CTN2007131A-/2',N'KMR16004681-2',100 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465',228 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-1',272 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-2',220 union all
select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-3',480 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465',57 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-1',68 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-2',55 union all
select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-3',120
Go
;WITH a
AS
(SELECT *,SUM([数量])OVER(PARTITION BY [物料编号] ORDER BY [单据编号],[批号] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumQty1 from #1)
,b AS
(SELECT *,SUM([数量])OVER(PARTITION BY [物料编号] ORDER BY [单据编号],[拆分后单据编号] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumQty2 from #2)
SELECT  b.[单据编号],b.[物料编号],b.[拆分后单据编号],a.批号,CASE WHEN a.SumQty1>b.SumQty2 THEN b.SumQty2 ELSE a.SumQty1 END-CASE WHEN b.SumQty2 - b.数量>a.SumQty1 - a.数量 THEN b.SumQty2 - b.数量 ELSE a.SumQty1 - a.数量 END AS 扣减数量
FROM    a
        INNER JOIN b ON a.物料编号 = b.物料编号
                        AND a.SumQty1 > b.SumQty2 - b.数量
                        AND b.SumQty2 > a.SumQty1 - a.数量
ORDER BY 1;

/*
单据编号	物料编号	拆分后单据编号	批号	扣减数量
KMR16004350	CTN2006164D-/36R1	KMR16004350	SC160218-06	67
KMR16004350	CTN2006164D-/36R1	KMR16004350-1	SC160394-06	56
KMR16004350	CTN2006164D-/36R1	KMR16004350-1	SC160407-05	34
KMR16004350	CTN2006164D-/36R1	KMR16004350-1	SC160416-07	48
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160218-07	23
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160394-07	56
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160407-06	34
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160410-01	5
KMR16004350	CTN2006165D-/36R	KMR16004350	SC160416-08	48
KMR16004681	CTN2007131A-/2	KMR16004681-1	SC160218-11	225
KMR16004681	CTN2007131A-/2	KMR16004681-2	SC160394-10	100
KMR16006465	CNR2006167A-/6R1	KMR16006465	SC160416-12	228
KMR16006465	CNR2006167A-/6R1	KMR16006465-1	SC160416-12	272
KMR16006465	CNR2006167A-/6R1	KMR16006465-2	SC160416-12	220
KMR16006465	CNR2006167A-/6R1	KMR16006465-3	SC160548-05	480
KMR16006465	CTN2006167A-6/24R3	KMR16006465	SC160416-12	57
KMR16006465	CTN2006167A-6/24R3	KMR16006465-1	SC160416-12	68
KMR16006465	CTN2006167A-6/24R3	KMR16006465-2	SC160416-12	55
KMR16006465	CTN2006167A-6/24R3	KMR16006465-3	SC160548-05	120
*/
中国风 2017-02-21
  • 打赏
  • 举报
 回复
SQL2012之前版本 可能参照07年回复的贴 http://bbs.csdn.net/topics/120010091
中国风 2017-02-21
  • 打赏
  • 举报
 回复
SQL2012之后的版本，用以下方法拆分效率高 之后版本，把CTE里SumQty改改就行了 
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([单据编号] nvarchar(23),[物料编号] nvarchar(23),[批号] nvarchar(23),[数量] int)
Insert #1
select N'a01',N'a02',N'a31',100 union all
select N'a01',N'a02',N'a32',200
Go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#2') is null
	drop table #2
Go
Create table #2([单据编号] nvarchar(23),[物料编号] nvarchar(23),[拆分后单据编号] nvarchar(24),[数量] int)
Insert #2
select N'a01',N'a02',N'a011',180 union all
select N'a01',N'a02',N'a012',120
Go
;WITH a
AS
(SELECT *,SUM([数量])OVER(PARTITION BY [物料编号] ORDER BY [批号] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumQty1 from #1)
,b AS
(SELECT *,SUM([数量])OVER(PARTITION BY [物料编号] ORDER BY [拆分后单据编号] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SumQty2 from #2)
SELECT  b.[单据编号],b.[物料编号],b.[拆分后单据编号],a.批号,CASE WHEN a.SumQty1>b.SumQty2 THEN b.SumQty2 ELSE a.SumQty1 END-CASE WHEN b.SumQty2 - b.数量>a.SumQty1 - a.数量 THEN b.SumQty2 - b.数量 ELSE a.SumQty1 - a.数量 END AS 扣减数量
FROM    a
        INNER JOIN b ON a.物料编号 = b.物料编号
                        AND a.SumQty1 > b.SumQty2 - b.数量
                        AND b.SumQty2 > a.SumQty1 - a.数量;

/*
单据编号	物料编号	拆分后单据编号	批号	扣减数量
a01	a02	a011	a31	100
a01	a02	a011	a32	80
a01	a02	a012	a32	120
*/
Tiger_Zhao 2017-02-21
  • 打赏
  • 举报
 回复
把你的语句贴出来，结果有什么问题？
lzw_0736 2017-02-21
  • 打赏
  • 举报
 回复
引用 7 楼 Tiger_Zhao 的回复:
把你的语句贴出来，结果有什么问题？
--Tiger_Zhao老师: 以下是完整测试语句, 查询得到的结果之总数是1358,而#MainOrder的总数[SUM(数量)]是2196,所以结果肯定不对 --我用的是SQL2005 if not object_id(N'Tempdb..#MainOrder') is null drop table #MainOrder Go Create table #MainOrder([单据编号] nvarchar(31),[物料编号] nvarchar(38),[批号] nvarchar(31),[数量] int) Insert #MainOrder select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160218-06',67 union all select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160394-06',56 union all select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160407-05',34 union all select N'KMR16004350',N'CTN2006164D-/36R1',N'SC160416-07',48 union all select N'KMR16004350',N'CTN2006165D-/36R',N'SC160218-07',23 union all select N'KMR16004350',N'CTN2006165D-/36R',N'SC160394-07',56 union all select N'KMR16004350',N'CTN2006165D-/36R',N'SC160407-06',34 union all select N'KMR16004350',N'CTN2006165D-/36R',N'SC160410-01',5 union all select N'KMR16004350',N'CTN2006165D-/36R',N'SC160416-08',48 union all select N'KMR16004681',N'CTN2007131A-/2',N'SC160218-11',225 union all select N'KMR16004681',N'CTN2007131A-/2',N'SC160394-10',100 union all select N'KMR16006465',N'CNR2006167A-/6R1',N'SC160416-12',720 union all select N'KMR16006465',N'CNR2006167A-/6R1',N'SC160548-05',480 union all select N'KMR16006465',N'CTN2006167A-6/24R3',N'SC160416-12',180 union all select N'KMR16006465',N'CTN2006167A-6/24R3',N'SC160548-05',120 Go if not object_id(N'Tempdb..#SubOrder') is null drop table #SubOrder Go Create table #SubOrder([单据编号] nvarchar(31),[物料编号] nvarchar(38),[拆分后单据编号] nvarchar(33),[数量] int) Insert #SubOrder select N'KMR16004350',N'CTN2006164D-/36R1',N'KMR16004350',67 union all select N'KMR16004350',N'CTN2006164D-/36R1',N'KMR16004350-1',138 union all select N'KMR16004350',N'CTN2006165D-/36R',N'KMR16004350',166 union all select N'KMR16004681',N'CTN2007131A-/2',N'KMR16004681-1',225 union all select N'KMR16004681',N'CTN2007131A-/2',N'KMR16004681-2',100 union all select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465',228 union all select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-1',272 union all select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-2',220 union all select N'KMR16006465',N'CNR2006167A-/6R1',N'KMR16006465-3',480 union all select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465',57 union all select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-1',68 union all select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-2',55 union all select N'KMR16006465',N'CTN2006167A-6/24R3',N'KMR16006465-3',120 Go ;with cte_1 as ( select 单据编号,物料编号,sum(a.数量) as [总数量] from #MainOrder a group by 单据编号,物料编号 ),cte_2 as ( select a.单据编号,a.物料编号,a.批号,case when b.数量-a.数量>0 then a.数量 else b.数量 end as 数量,b.拆分后单据编号,b.数量-a.数量 as [差值] ,c.总数量-case when b.数量-a.数量>0 then a.数量 else b.数量 end as [剩余数量] from #MainOrder a inner join #SubOrder b on b.单据编号=a.单据编号 and b.物料编号=a.物料编号 and not exists(select 1 from dbo.#SubOrder x where x.单据编号=b.单据编号 and x.物料编号=b.物料编号 and x.拆分后单据编号<b.拆分后单据编号) inner join cte_1 c on c.单据编号=a.单据编号 and c.物料编号=a.物料编号 where not exists(select 1 from #MainOrder x where x.单据编号=a.单据编号 and x.物料编号=a.物料编号 and x.批号<a.批号) union all select a.单据编号,a.物料编号,b.批号,case when b.数量-a.差值>0 then a.差值 else b.数量 end as 数量,a.拆分后单据编号,a.差值-b.数量 as [差值],a.剩余数量-case when b.数量-a.差值>0 then a.差值 else b.数量 end as [剩余数量] from cte_2 a inner join #MainOrder b on b.单据编号=a.单据编号 and b.物料编号=a.物料编号 and b.批号>a.批号 and not exists(select 1 from dbo.#MainOrder x where x.单据编号=b.单据编号 and x.物料编号=b.物料编号 and x.批号>a.批号 and x.批号<b.批号) where a.差值>0 and a.剩余数量>0 union all select a.单据编号,a.物料编号,a.批号,case when b.数量+a.差值>0 then abs(a.差值) else b.数量 end as 数量,b.拆分后单据编号,b.数量+a.差值 as [差值],a.剩余数量-case when b.数量+a.差值>0 then a.差值 else b.数量 end as [剩余数量] from cte_2 a inner join #SubOrder b on b.单据编号=a.单据编号 and b.物料编号=a.物料编号 and b.拆分后单据编号>a.拆分后单据编号 and not exists(select 1 from dbo.#SubOrder x where x.单据编号=b.单据编号 and x.物料编号=b.物料编号 and x.拆分后单据编号>a.拆分后单据编号 and x.拆分后单据编号<b.拆分后单据编号) where a.差值<0 and a.剩余数量>0 ) select a.单据编号,a.物料编号,a.拆分后单据编号,a.批号,a.数量 from cte_2 a order by a.单据编号,a.物料编号,a.批号,a.拆分后单据编号
lzw_0736 2017-02-20
  • 打赏
  • 举报
 回复
[quote=引用 2 楼 DVD_01 的回复:] 分单的处理，可以参考： 非常感谢老师的回复，因为工作忙碌没有及时测试，经实际测试，基本符合要求，但有以下情况处理结果不对： 表一 单据编号 物料编号 批号 数量 KMR16004350 CTN2006164D-/36R1 SC160218-06 67 KMR16004350 CTN2006164D-/36R1 SC160394-06 56 KMR16004350 CTN2006164D-/36R1 SC160407-05 34 KMR16004350 CTN2006164D-/36R1 SC160416-07 48 KMR16004350 CTN2006165D-/36R SC160218-07 23 KMR16004350 CTN2006165D-/36R SC160394-07 56 KMR16004350 CTN2006165D-/36R SC160407-06 34 KMR16004350 CTN2006165D-/36R SC160410-01 5 KMR16004350 CTN2006165D-/36R SC160416-08 48 KMR16004681 CTN2007131A-/2 SC160218-11 225 KMR16004681 CTN2007131A-/2 SC160394-10 100 KMR16006465 CNR2006167A-/6R1 SC160416-12 720 KMR16006465 CNR2006167A-/6R1 SC160548-05 480 KMR16006465 CTN2006167A-6/24R3 SC160416-12 180 KMR16006465 CTN2006167A-6/24R3 SC160548-05 120 表二 单据编号 物料编号 拆分后单据编号 数量 KMR16004350 CTN2006164D-/36R1 KMR16004350 67 KMR16004350 CTN2006164D-/36R1 KMR16004350-1 138 KMR16004350 CTN2006165D-/36R KMR16004350 166 KMR16004681 CTN2007131A-/2 KMR16004681-1 225 KMR16004681 CTN2007131A-/2 KMR16004681-2 100 KMR16006465 CNR2006167A-/6R1 KMR16006465 228 KMR16006465 CNR2006167A-/6R1 KMR16006465-1 272 KMR16006465 CNR2006167A-/6R1 KMR16006465-2 220 KMR16006465 CNR2006167A-/6R1 KMR16006465-3 480 KMR16006465 CTN2006167A-6/24R3 KMR16006465 57 KMR16006465 CTN2006167A-6/24R3 KMR16006465-1 68 KMR16006465 CTN2006167A-6/24R3 KMR16006465-2 55 KMR16006465 CTN2006167A-6/24R3 KMR16006465-3 120 上面的数据得不到我想要的结果，请老师帮忙再看一下. 谢谢！
Andy-W 2017-02-09
  • 打赏
  • 举报
 回复
引用 3 楼 Tiger_Zhao 的回复:
大神求解：把一张表的数据根据另一张表里面进行分配 问的人很多，给了答案结帖的不多。
3年前的帖子。
Tiger_Zhao 2017-02-09
  • 打赏
  • 举报
 回复
大神求解：把一张表的数据根据另一张表里面进行分配
问的人很多，给了答案结帖的不多。
Tiger_Zhao 2017-02-09
  • 打赏
  • 举报
 回复
问题是每年都有人问相似的问题，烦了，直接给链接了。
不知道是哪个老师的作业？
如果是哪个公司的业务——那么太恐怖了。
Andy-W 2017-02-08
  • 打赏
  • 举报
 回复
分单的处理，可以参考： 

use tempdb
go

--> --> (Andy)生成測試數據
 
if not object_ID('Tempdb..MainOrder') is null
	drop table MainOrder

if not object_ID('Tempdb..SubOrder') is null
	drop table SubOrder
Go
Create table MainOrder([单据编号] nvarchar(3),[物料编号] nvarchar(3),[批号] nvarchar(3),[数量] int)
Insert MainOrder
select 'a01','a02','a31',100 union all --（MainOrder Vs MainOrder） 2行对2行 情况 
select 'a01','a02','a32',200 union all
select 'b01','b01','b31',50  union all --（MainOrder Vs MainOrder）多行对2行 情况
select 'b01','b01','b32',150 union all
select 'b01','b01','b33',200 union all
select 'b01','b01','b34',100 union all
select 'c01','c01','c31',200 union all --（MainOrder Vs MainOrder）2行对多行 情况
select 'c01','c01','c32',100 

go
Create table SubOrder([单据编号] nvarchar(3),[物料编号] nvarchar(3),[拆分后单据编号] nvarchar(4),[数量] int)
Insert SubOrder
select 'a01','a02','a011',180 union all
select 'a01','a02','a012',120 union all
select 'b01','b01','b011',30  union all
select 'b01','b01','b012',470 union all
select 'c01','c01','c011',50 union all
select 'c01','c01','c012',60 union all
select 'c01','c01','c013',190 
go

Select * from MainOrder
Select * from SubOrder





;with cte_1 as (
select 单据编号,物料编号,sum(a.数量) as [总数量] from MainOrder a group by 单据编号,物料编号
),cte_2 as (
	select  a.单据编号,a.物料编号,a.批号,case when b.数量-a.数量>0 then a.数量 else b.数量 end as 数量,b.拆分后单据编号,b.数量-a.数量 as [差值] ,c.总数量-case when b.数量-a.数量>0 then a.数量 else b.数量 end as [剩余数量]
		from MainOrder a
			inner join SubOrder  b on b.单据编号=a.单据编号 and b.物料编号=a.物料编号
				and not exists(select 1 from dbo.SubOrder x where x.单据编号=b.单据编号 and x.物料编号=b.物料编号 and x.拆分后单据编号<b.拆分后单据编号)
			inner join cte_1 c on c.单据编号=a.单据编号 and c.物料编号=a.物料编号				
		where not exists(select 1 from MainOrder x where x.单据编号=a.单据编号 and x.物料编号=a.物料编号 and x.批号<a.批号)
	union all 
	select a.单据编号,a.物料编号,b.批号,case when b.数量-a.差值>0 then a.差值 else b.数量 end as 数量,a.拆分后单据编号,a.差值-b.数量 as [差值],a.剩余数量-case when b.数量-a.差值>0 then a.差值 else b.数量 end as [剩余数量]
		from cte_2 a
			inner join MainOrder b on b.单据编号=a.单据编号 and b.物料编号=a.物料编号 and b.批号>a.批号
				and not exists(select 1 from dbo.MainOrder x where x.单据编号=b.单据编号 and x.物料编号=b.物料编号 and x.批号>a.批号 and x.批号<b.批号)
				where a.差值>0 and a.剩余数量>0	
	union all 
	select a.单据编号,a.物料编号,a.批号,case when b.数量+a.差值>0 then abs(a.差值) else b.数量 end as 数量,b.拆分后单据编号,b.数量+a.差值 as [差值],a.剩余数量-case when b.数量+a.差值>0 then a.差值 else b.数量 end as [剩余数量]
		from cte_2 a
			inner join SubOrder b on b.单据编号=a.单据编号 and b.物料编号=a.物料编号 and b.拆分后单据编号>a.拆分后单据编号
				and not exists(select 1 from dbo.SubOrder x where x.单据编号=b.单据编号 and x.物料编号=b.物料编号 and x.拆分后单据编号>a.拆分后单据编号 and x.拆分后单据编号<b.拆分后单据编号)
				where a.差值<0 and a.剩余数量>0	
)
select a.单据编号,a.物料编号,a.拆分后单据编号,a.批号,a.数量  from cte_2 a order by a.单据编号,a.物料编号,a.批号,a.拆分后单据编号


/*
表三
单据编号 物料编号 拆分后单据编号 批号   数量
---- ---- ------- ---- -----------
a01  a02  a011    a31  100
a01  a02  a011    a32  80
a01  a02  a012    a32  120
b01  b01  b011    b31  30
b01  b01  b012    b31  20
b01  b01  b012    b32  150
b01  b01  b012    b33  200
b01  b01  b012    b34  100
c01  c01  c011    c31  50
c01  c01  c012    c31  60
c01  c01  c013    c31  90
c01  c01  c013    c32  100
*/
这里我列了三种情况，望楼主参考。
Andy-W 2017-02-08
  • 打赏
  • 举报
 回复
1条语句很难实现，毕竟还有表1和表2的增量统计和判断
怎么在数据库中同时执行两条sql语句并且当一条语句报错另一条语句就不再执行？
//回轮(执行时返回两条sql语句或多条sql语句，并不执行两条或多条sql语句) commit;//提交(执行时如果一条语句出错其他的语句将不再执行，停止执行) 实用于银行转账，扣钱时对方账户里加钱，这个操作
一条SQL语句在MySQL中执行过程全解析
本篇文章会分析一个 sql 语句在 MySQL 中的执行流程，包括 sql 的查询在 MySQL 内部会怎么流转，sql 语句的更新是怎么完成的。
MySQL多条SQL语句同时执行的解决办法
然而，MySQL默认情况下不允许一次执行多条SQL语句，这可能导致我们在执行包含多条SQL语句的脚本或存储过程时遇到问题。通过将多条SQL语句封装在一个存储过程中，我们可以实现同时执行多条语句。这样，我们可以将多条...
MySQL多条SQL语句合并实现
块，可以将多条SQL语句作为一个事务进行执行，确保它们要么全部成功执行，要么全部不执行。关键字，在事务块执行完最后一条 SQL 语句后，MySQL 会隐式地将事务提交或回滚（取决于 SQL 语句的执行结果）。来将多条 ...
一条 SQL 语句是如何执行的
sql 语句执行流程
疑难问题

22,302

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章