在SQL Server中汇总，在JOIN的一侧有多个行，另一侧的一侧作为比较

Question

我有一个试图找出答案的情况。我正在尝试比较尺寸级别的两组数据集。似乎很简单，但问题在某些情况下可以在方程式一侧的两条线上拆分，因此我将有两行250与500行相比。我的写作似乎不起作用。我已经发布了下面的查询，并提供了一些输出。在第一个结果材料中，d68154尺寸l在查询的" M"侧有两条线，总计500，一条线上的一条线在查询的侧面，总计500。在我的理想情况下，当我的情况下，这个结果不会返回不仅要比较总和实际不同的地方。有任何想法吗？谢谢。

查询：

SELECT
    M.STOCK_NUMBER AS [PO_NUMBER],
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL,
    A.GRID_VALUE,
    SUM(M.QUANTITY) AS [PO_QTY],
    SUM(A.QUANTITY) AS [SO_QTY],
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE AS [ETA],
    M.PLANT_CODE
FROM VW_MRP_ALLOCATION M
JOIN VW_ARUN_NORM_NEW A   
    ON M.SALES_ORDER_NUMBER = A.SALES_ORDER_NUMBER AND
       M.SALES_ORDER_ITEM_NUMBER = A.SALES_ORDER_ITEM_NUMBER AND
       M.STOCK_NUMBER = A.SAP_PO_NUMBER AND
       M.SIZE_LITERAL = A.GRID_VALUE AND
       M.MATERIAL = A.MATERIAL_NUMBER
WHERE
    M.REQUIREMENT_TYPE = 'KE' AND
    M.STOCK_TYPE NOT IN ('A','C') AND
    M.STOCK_NUMBER IS NOT NULL 
GROUP BY
    M.STOCK_NUMBER,
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL, 
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE,
    M.PLANT_CODE, 
    A.GRID_VALUE
HAVING
    SUM(M.QUANTITY) <> SUM(A.QUANTITY)
ORDER BY
    M.STOCK_NUMBER,
    M.SIZE_LITERAL;

结果：

PO_NUMBER    MATERIAL           MATERIAL_DESCRIPTION                     SIZE_LITERAL GRID_VALUE PO_QTY                                  SO_QTY                                  SALES_ORDER_NUMBER SALES_ORDER_ITEM_NUMBER                 CUSTOMER_NAME                       ETA                     PLANT_CODE
------------ ------------------ ---------------------------------------- ------------ ---------- --------------------------------------- --------------------------------------- ------------------ --------------------------------------- ----------------------------------- ----------------------- ----------
0283127530   D68154             GRAPHIC BRA         BLACK/WHITE          L            L          250                                     500                                     6194098835         10                                      ROSS STORES INC                     2018-03-31 00:00:00.000 6010
0283127530   D68154             GRAPHIC BRA         BLACK/WHITE          L            L          250                                     500                                     6194098835         10                                      ROSS STORES INC                     2018-04-28 00:00:00.000 6010
0283127530   D68154             GRAPHIC BRA         BLACK/WHITE          M            M          250                                     500                                     6194098835         10                                      ROSS STORES INC                     2018-03-31 00:00:00.000 6010
0283127530   D68154             GRAPHIC BRA         BLACK/WHITE          M            M          250                                     500                                     6194098835         10                                      ROSS STORES INC                     2018-04-28 00:00:00.000 6010
0283127560   D68153             BOLT TANK           DGSOGR               L            L          250                                     500                                     6194098839         10                                      ROSS STORES INC                     2018-03-31 00:00:00.000 6010
0283127560   D68153             BOLT TANK           DGSOGR               L            L          250                                     500                                     6194098839         10                                      ROSS STORES INC                     2018-04-28 00:00:00.000 6010

Answer 1

尝试使用VW_ARUN_NORM_NEW的子查询。作为变体

SELECT
    ...    
FROM VW_MRP_ALLOCATION M
JOIN
  (
    SELECT
      SALES_ORDER_NUMBER,
      SALES_ORDER_ITEM_NUMBER,
      SAP_PO_NUMBER,
      GRID_VALUE,
      MATERIAL_NUMBER,
      SUM(QUANTITY) QUANTITY
    FROM VW_ARUN_NORM_NEW
    GROUP BY SALES_ORDER_NUMBER,SALES_ORDER_ITEM_NUMBER,SAP_PO_NUMBER,GRID_VALUE,
      MATERIAL_NUMBER
  ) A   
    ON M.SALES_ORDER_NUMBER = A.SALES_ORDER_NUMBER AND
       M.SALES_ORDER_ITEM_NUMBER = A.SALES_ORDER_ITEM_NUMBER AND
       M.STOCK_NUMBER = A.SAP_PO_NUMBER AND
       M.SIZE_LITERAL = A.GRID_VALUE AND
       M.MATERIAL = A.MATERIAL_NUMBER
WHERE
    M.REQUIREMENT_TYPE = 'KE' AND
    M.STOCK_TYPE NOT IN ('A','C') AND
    M.STOCK_NUMBER IS NOT NULL 
GROUP BY
    M.STOCK_NUMBER,
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL, 
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE,
    M.PLANT_CODE, 
    A.GRID_VALUE
HAVING
    SUM(M.QUANTITY) <> SUM(A.QUANTITY)
ORDER BY
    M.STOCK_NUMBER,
    M.SIZE_LITERAL;

作为变体，您可以在查询中使用DISTINCT，但我认为这不是一个好方法。

尝试检查更多变体

SELECT
    M.STOCK_NUMBER AS [PO_NUMBER],
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL,
    A.GRID_VALUE,
    SUM(M.QUANTITY) [PO_QTY],
    SUM(A.QUANTITY) AS [SO_QTY],
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE AS [ETA],
    M.PLANT_CODE,
    M.STOCK_TYPE
FROM
  (
    SELECT
      STOCK_NUMBER,MATERIAL,MATERIAL_DESCRIPTION,SIZE_LITERAL,SALES_ORDER_NUMBER,SALES_ORDER_ITEM_NUMBER,CUSTOMER_NAME,PLANNED_RECEIPT_DATE,PLANT_CODE,STOCK_TYPE,
      QUANTITY,
      SUM(QUANTITY) OVER(PARTITION BY STOCK_NUMBER,MATERIAL,SALES_ORDER_NUMBER,SALES_ORDER_ITEM_NUMBER,SIZE_LITERAL) SUM_QUANTITY
    FROM VW_MRP_ALLOCATION
    WHERE REQUIREMENT_TYPE = 'KE' AND
        STOCK_TYPE NOT IN ('A','C') AND
        STOCK_NUMBER IS NOT NULL
  ) M
JOIN
  (
    SELECT
      SALES_ORDER_NUMBER,SALES_ORDER_ITEM_NUMBER,MATERIAL_NUMBER,GRID_VALUE,SAP_PO_NUMBER,
      QUANTITY,
      SUM(QUANTITY) OVER(PARTITION BY SALES_ORDER_NUMBER,SALES_ORDER_ITEM_NUMBER,MATERIAL_NUMBER,GRID_VALUE,SAP_PO_NUMBER) SUM_QUANTITY
    FROM VW_ARUN_NORM_NEW
    WHERE STOCK_TYPE IN ('L','B')
  ) A
ON M.SALES_ORDER_NUMBER = A.SALES_ORDER_NUMBER AND
    M.SALES_ORDER_ITEM_NUMBER = A.SALES_ORDER_ITEM_NUMBER AND
    M.STOCK_NUMBER = A.SAP_PO_NUMBER AND
    M.SIZE_LITERAL = A.GRID_VALUE AND
    M.MATERIAL = A.MATERIAL_NUMBER
WHERE M.SUM_QUANTITY <> A.SUM_QUANTITY
GROUP BY M.STOCK_NUMBER,
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL,
    A.GRID_VALUE,
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE,
    M.PLANT_CODE,
    M.STOCK_TYPE
ORDER BY
    M.STOCK_NUMBER,
    M.SIZE_LITERAL;

Answer 2

好的，因此数据集的差异比我最初想象的要多。下面的解决方案似乎可以完美(到目前为止(。这很困难，因为我们的数据库是在全球管理的，有时会改变最终用户的偷偷摸摸。这是一个查询的野兽，需要5分钟的时间运行，但到目前为止，它返回了正确的结果 - 参考我们有4种股票类型(a，b，c，l( - 目标是排除A和C-我做了它以某种方式围绕：

SELECT
    M.STOCK_NUMBER AS [PO_NUMBER],
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL,
    A.GRID_VALUE,
    M1.QUANTITY [PO_QTY],
    SUM(A.QUANTITY) AS [SO_QTY],
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE AS [ETA],
    M.PLANT_CODE,
    M.STOCK_TYPE
FROM VW_MRP_ALLOCATION M
JOIN VW_ARUN_NORM_NEW A   
    ON M.SALES_ORDER_NUMBER = A.SALES_ORDER_NUMBER AND
       M.SALES_ORDER_ITEM_NUMBER = A.SALES_ORDER_ITEM_NUMBER AND
       M.STOCK_NUMBER = A.SAP_PO_NUMBER AND
       M.SIZE_LITERAL = A.GRID_VALUE AND
       M.MATERIAL = A.MATERIAL_NUMBER
JOIN   (SELECT M1.STOCK_NUMBER,M1.MATERIAL,M1.SALES_ORDER_NUMBER,M1.SALES_ORDER_ITEM_NUMBER,M1.SIZE_LITERAL,SUM(M1.QUANTITY) AS [QUANTITY] FROM VW_MRP_ALLOCATION M1 
        GROUP BY M1.STOCK_NUMBER,M1.MATERIAL,M1.SALES_ORDER_NUMBER,M1.SALES_ORDER_ITEM_NUMBER,M1.SIZE_LITERAL,M1.SALES_ORDER_ITEM_NUMBER) AS [M1]
ON     M.STOCK_NUMBER = M1.STOCK_NUMBER AND
       M.MATERIAL = M1.MATERIAL AND 
       M1.MATERIAL = A.MATERIAL_NUMBER AND
       M1.SIZE_LITERAL = A.GRID_VALUE AND 
       M1.SALES_ORDER_NUMBER = A.SALES_ORDER_NUMBER AND 
       M1.SALES_ORDER_ITEM_NUMBER = A.SALES_ORDER_ITEM_NUMBER
JOIN   (SELECT A1.SALES_ORDER_NUMBER,A1.SALES_ORDER_ITEM_NUMBER,A1.MATERIAL_NUMBER,A1.GRID_VALUE,A1.SAP_PO_NUMBER,SUM(A1.QUANTITY) AS [QUANTITY] FROM VW_ARUN_NORM_NEW A1
        WHERE STOCK_TYPE IN ('L','B')
        GROUP BY A1.SALES_ORDER_NUMBER,A1.SALES_ORDER_ITEM_NUMBER,A1.MATERIAL_NUMBER,A1.GRID_VALUE,A1.SAP_PO_NUMBER) AS [A1]
ON     A1.SALES_ORDER_NUMBER = M1.SALES_ORDER_NUMBER AND 
       A1.SALES_ORDER_ITEM_NUMBER = M1.SALES_ORDER_ITEM_NUMBER AND 
       A1.MATERIAL_NUMBER = M1.MATERIAL AND 
       A1.GRID_VALUE = M1.SIZE_LITERAL 
WHERE
    M.REQUIREMENT_TYPE = 'KE' AND
    M.STOCK_TYPE NOT IN ('A','C') AND
    M.STOCK_NUMBER IS NOT NULL
GROUP BY
    M.STOCK_NUMBER,
    M.MATERIAL, 
    M.MATERIAL_DESCRIPTION, 
    M.SIZE_LITERAL, 
    M.SALES_ORDER_NUMBER, 
    M.SALES_ORDER_ITEM_NUMBER, 
    M.CUSTOMER_NAME, 
    M.PLANNED_RECEIPT_DATE,
    M.PLANT_CODE, 
    A.GRID_VALUE,
    M.STOCK_TYPE,
    M1.QUANTITY,
    A1.QUANTITY
HAVING M1.QUANTITY <> A1.QUANTITY
ORDER BY
    M.STOCK_NUMBER,
    M.SIZE_LITERAL;