在SQL中按＆分组

Hardik Parmar 发表于 Dev

哈迪克·帕玛（Hardik Parmar）

我对这个GROUP BY＆Union声明非常困惑。

现在，我正在获取此O / P。

在此处输入图片说明

但是我希望我的输出看起来像这样：

   Vendor Name          Purchase Order         Req Qty        Inward Qty
   ----------------------------------------------------------------------       
   Philips Lighting      PO20140918133011        350             350
   (I) Pvt Ltd

要求：

如果我的供应商为空，那么我也必须根据采购订单进行分组。但是我得到的输出不能满足所有要求。有什么办法可以仅根据采购订单进行分组？

**询问： **

SELECT
    vendor_name,
    purchase_order_no,  
    SUM(ISNULL(InwardQty,0)) AS InwardQty,
    SUM(ISNULL(ReqQty,0)) ReqQty
FROM
(
    SELECT 
        vendor_name,
        pm.purchase_order_no,
        ISNULL(SUM(PIIM.qty),0) AS ReqQty,
        0 InwardQty
    FROM  
        RS_Purchase_Order_Master AS PM
        LEFT OUTER JOIN RS_Purchase_Invoice_Info_Master AS PIIM ON PIIM.purchase_order_no = PM.purchase_order_no
        LEFT OUTER JOIN RS_Vendor_Master AS VM ON VM.vendor_id = PM.vendor_id
    WHERE 
        IsPicreated = 1
    GROUP BY 
        vendor_name,
        pm.purchase_order_no

    UNION

    SELECT
        NULL AS vendor_name,
        purchase_order_no,
        0 AS ReqQty,
        ISNULL(SUM(qty),0) AS InwardQty
    FROM
        RS_GIN_Master       
    GROUP BY
        purchase_order_no
)
AS A
GROUP BY
    vendor_name,
    purchase_order_no

任何帮助或建议，将不胜感激。

先感谢您

弯头

您不需要创建临时表。只需从GROUP BY中删除vendor_name，然后添加MAX（vendor_name）。

SELECT
    MAX(vendor_name) AS vendor_name,
    purchase_order_no,  
    SUM(ISNULL(InwardQty,0)) AS InwardQty,
    SUM(ISNULL(ReqQty,0)) AS ReqQty
FROM
(
    SELECT 
        vendor_name,
        pm.purchase_order_no,
        ISNULL(SUM(PIIM.qty),0) AS ReqQty,
        0 InwardQty
    FROM  
       RS_Purchase_Order_Master AS PM
       LEFT OUTER JOIN RS_Purchase_Invoice_Info_Master AS PIIM ON PIIM.purchase_order_no = PM.purchase_order_no
       LEFT OUTER JOIN RS_Vendor_Master AS VM ON VM.vendor_id = PM.vendor_id
    WHERE 
        IsPicreated = 1
    GROUP BY 
        vendor_name,
        pm.purchase_order_no

    UNION

    SELECT
        NULL AS vendor_name,
        purchase_order_no,
        0 AS ReqQty,
        ISNULL(SUM(qty),0) AS InwardQty
    FROM
        RS_GIN_Master       
    GROUP BY
        purchase_order_no
)
AS A
GROUP BY
    purchase_order_no