代码之家 › 专栏 › 技术社区 › chefsmart

将联接与聚合一起使用,并在不存在聚合时检索行

aggregate-functions join mysql

0

chefsmart · 技术社区 · 13 年前

以下对MySQL表的查询返回purchaseorder表中的行,这些行在deliveryorder表中具有相应的条目。即使deliveryorder表中不存在对应的行,我如何构造此查询以从purchaseorder表中获取行?如果用户想看到sql表CREATE语句,我可以发布这些语句,但我现在不发布,因为这真的让问题太大了。

SELECT
    `purchaseorder`.`id` AS `po_id`,
    `purchaseorder`.`order_quantity` AS `po_order_quantity`,
    `purchaseorder`.`applicable_approved_unit_rate` AS `po_unit_rate`,
    `purchaseorder`.`applicable_sales_tax_rate` AS `po_tax_rate`,
    `purchaseorder`.`order_date` AS `po_order_date`,
    `purchaseorder`.`remarks` AS `po_remarks`,
    `purchaseorder`.`is_open` AS `po_is_open`,
    `purchaseorder`.`is_active` AS `po_is_active`,
    `purchaseorder`.`approved_rate_id` AS `po_app_rate_id`,

    `supplier`.`name` AS `sup_name`,

    SUM(`deliveryorder`.`quantity`) AS `total_ordered`

    FROM `purchaseorder`
    LEFT JOIN `deliveryorder` ON (`deliveryorder`.`purchase_order_id` = `purchaseorder`.`id`)
    INNER JOIN `approvedrate` ON (`purchaseorder`.`approved_rate_id` = `approvedrate`.`id`)
    INNER JOIN `supplier` ON (`approvedrate`.`supplier_id` = `supplier`.`id`)

    WHERE (
            `purchaseorder`.`is_active` = 1
            AND `purchaseorder`.`is_open` = 1
            AND `deliveryorder`.`is_active` = 1
            AND `approvedrate`.`material_id` = 2
           )
    HAVING `purchaseorder`.`order_quantity` >= `total_ordered` + 1

2 回复 | 直到 13 年前

1

0

Strawberry 13 年前

你有一个聚合函数,但没有GROUP BY子句,这是wierd,但无论如何——像这样的东西?糟糕-已编辑。。。

SELECT po.id po_id
     , po.order_quantity po_order_quantity
     , po.applicable_approved_unit_rate po_unit_rate
     , po.applicable_sales_tax_rate po_tax_rate
     , po.order_date po_order_date
     , po.remarks po_remarks
     , po.is_open po_is_open
     , po.is_active po_is_active
     , po.approved_rate_id po_app_rate_id
     , s.name sup_name
     , SUM(do.quantity) total_ordered
  FROM purchaseorder po
  LEFT 
  JOIN deliveryorder do 
    ON do.purchase_order_id = po.  
   AND do.is_active = 1
  LEFT
  JOIN approvedrate ar
    ON ar.id = po.approved_rate_id 
   AND ar.material_id = 2
  LEFT
  JOIN supplier s
    ON s.id = ar.supplier_id 
 WHERE po.is_active = 1
   AND po.is_open = 1
HAVING po.order_quantity >= total_ordered + 1

2

0

chefsmart 13 年前

我不知道如何在一个查询中获得所有想要的结果,但最终使用了以下两个查询来满足我的要求:-

第一个查询

        SELECT
            pot.`id` AS `po_id`,
            pot.`order_quantity` AS `po_order_quantity`,
            pot.`applicable_approved_unit_rate` AS `po_unit_rate`,
            pot.`applicable_sales_tax_rate` AS `po_tax_rate`,
            pot.`is_open` AS `po_is_open`,
            pot.`is_active` AS `po_is_active`,

            st.`id` AS `sup_id`,
            st.`name` AS `sup_name`,

            SUM(dot.`quantity`) AS `total_ordered`
        FROM `purchaseorder` pot
            INNER JOIN `deliveryorder` dot ON (dot.`purchase_order_id` = pot.`id`)
            INNER JOIN `approvedrate` art ON (pot.`approved_rate_id` = art.`id`)
            INNER JOIN `supplier` st ON (art.`supplier_id` = st.`id`)
        WHERE (
                pot.`is_active` = 1
                AND pot.`is_open` = 1
                AND art.`material_id` = @materialid
                AND art.`in_effect` = 1
                AND art.`is_active` = 1
                AND dot.`is_active` = 1
                AND st.`is_active` = 1
               )
        HAVING pot.`order_quantity` >= `total_ordered` + @materialquantity

第二次查询

        SELECT
            pot.`id` AS `po_id`,
            pot.`order_quantity` AS `po_order_quantity`,
            pot.`applicable_approved_unit_rate` AS `po_unit_rate`,
            pot.`applicable_sales_tax_rate` AS `po_tax_rate`,
            pot.`is_open` AS `po_is_open`,
            pot.`is_active` AS `po_is_active`,

            st.`id` AS `sup_id`,
            st.`name` AS `sup_name`,

            0 AS `total_ordered`
        FROM `purchaseorder` pot
            INNER JOIN `approvedrate` art ON (pot.`approved_rate_id` = art.`id`)
            INNER JOIN `supplier` st ON (art.`supplier_id` = st.`id`)
        WHERE (
                pot.`is_active` = 1
                AND pot.`is_open` = 1
                AND art.`material_id` = @materialid
                AND art.`in_effect` = 1
                AND art.`is_active` = 1
                AND st.`is_active` = 1
                AND pot.`order_quantity` >= @materialquantity
                AND pot.`id` NOT IN
                    (
                      SELECT dot.`purchase_order_id`
                      FROM `deliveryorder` dot
                      WHERE dot.is_active = 1
                    )
               )