mysql 在单个查询中比较销售/购买

Question

我们有一个专有的销售系统，我们已经使用了一段时间了。最近我们添加了“购买”方面，以便我们可以比较匹配产品的平均购买/销售价格以及查看库存状况。

在 MySQL 中，我有 2 个表:tblPurchases 和 tblSalesDetail。

每个字段都有产品ID、数量、价格和运费字段(都是数字)。

我想查询每个产品 ID:

[product ID] [total sum purchase quantity] [avg purchase price] [avg purchase freight] [total sale quantity] [avg sale price] [avg sale freight]

我当前的sql语句如下所示

select 
    tblPurchases.productID,
    tblSalesDetail.productNo,
    sum(tblPurchases.quantity) as pQuantity,
    avg(tblPurchases.price) as pPrice,
    avg(tblPurchases.freight) as pFreight,
    sum(tblSalesDetail.quantity) as sQuantity,
    format(avg(tblSalesDetail.unitPrice),2) as sPrice,
    format(avg(tblSalesDetail.freight),2) as sFreight
from
    tblPurchases
left join tblSalesDetail
    on tblPurchases.productID=tblSalesDetail.productNo
group by tblPurchases.productID

但只有当双方的产品 ID 匹配时它才能正常工作。我希望它还显示没有匹配的购买产品 ID 的产品 ID 的销售情况。

通过单个查询可以实现这一点吗？

============

已编辑

新查询如下所示

select 
  ps.productID, 
  ps.productNo,
  sum(ps.pQuantity) as pQuantity, 
  avg(ps.pPrice) as pPrice, 
  avg(ps.pFreight) as pFreight,
  sum(sQuantity) as sQuantity, 
  avg(sPrice) as sPrice, 
  avg(sFreight) as sFreight
from (
        (select productID, 
                null as productNo,
                quantity as pQuantity, 
                price as pPrice, 
                freight as pFreight,
                NULL as sQuantity, 
                NULL as sPrice, 
                NULL as sFreight
         from tblPurchases)  

      union all

      (select null as productID, 
              productNo, 
              NULL as pQuantity,
              NULL as pPrice,
              null as pFreight,
              quantity as sQuantity, 
              unitPrice as sPrice, 
              freight as sFreight
       from tblSalesDetail
      )
     ) as ps
group by ps.productID

但是我得到了 2 行，所有不匹配的销售额都被平均在一行上。

如果我运行每个单独的查询，例如

select null as productID, 
                  productNo, 
                  NULL as pQuantity,
                  NULL as pPrice,
                  null as pFreight,
                  quantity as sQuantity, 
                  unitPrice as sPrice, 
                  freight as sFreight
           from tblSalesDetail

我得到了我期望得到的结果(大约 5000 行)。

==============

已解决

select 
*
from (
      (select   tblPurchases.productID as pProduct, 
                tblSalesDetail.productNo as sProduct,
                sum(tblPurchases.quantity) as pQuantity, 
                avg(tblPurchases.price) as pPrice, 
                avg(tblPurchases.freight) as pFreight,
                tblSalesDetail.quantity as sQuantity, 
                tblSalesDetail.unitPrice as sPrice, 
                tblSalesDetail.freight as sFreight
      from tblPurchases
      left join tblSalesDetail on tblPurchases.productID=tblSalesDetail.productNo
      group by tblPurchases.productID)   

      union all

      (select tblPurchases.productID as pProduct, 
              tblSalesDetail.productNo, 
              tblPurchases.quantity as pQuantity,
              tblPurchases.price as pPrice,
              tblPurchases.freight as pFreight,
              sum(tblSalesDetail.quantity) as sQuantity, 
              avg(tblSalesDetail.unitPrice) as sPrice, 
              avg(tblSalesDetail.freight) as sFreight
       from tblSalesDetail
       left join tblPurchases on tblPurchases.productID=tblSalesDetail.productNo
       group by tblSalesDetail.productNo)

     ) as ps

Answer 1

这是因为您正在执行LEFT OUTER JOIN TO tblSalesDetail，这给出了双方匹配的所有实例，以及没有销售详细信息的购买实例(不是相反)。两种选择:

向我提供所有销售详细信息，但仅提供匹配的购买

SELECT Purchases.productID AS productID
    ,Sales.productNo AS productNo
    ,SUM(Purchases.quantity) AS pQuantity
    ,AVG(Purchases.price) AS pPrice
    ,AVG(Purchases.freight) AS pFreight
    ,SUM(Sales.quantity) AS sQuantity
    ,FORMAT(AVG(Sales.unitPrice),2) AS sPrice
    ,FORMAT(AVG(Sales.freight),2) AS sFreight
FROM tblSalesDetail AS Sales
LEFT OUTER JOIN tblPurchases AS Purchases
    ON Purchases.productID = Sales.productNo
GROUP BY Purchases.productID

只需切换表，tblSalesDetail 就成为驱动程序。

向我提供所有销售详细信息和所有购买情况，无论匹配情况如何

注意:已编辑，因为 MySQL 不支持 FULL OUTER JOIN。

SELECT productID
    ,productNo
    ,SUM(pQuantity) AS pQuantity
    ,AVG(pPrice) AS pPrice
    ,AVG(pFreight) AS pFreight
    ,SUM(sQuantity) AS sQuantity
    ,FORMAT(AVG(sPrice),2) AS sPrice
    ,FORMAT(AVG(sFreight),2) AS sFreight
FROM (
    SELECT Purchases.productID AS productID
        ,Sales.productNo AS productNo
        ,Purchases.quantity AS pQuantity
        ,Purchases.price AS pPrice
        ,Purchases.freight AS pFreight
        ,Sales.quantity AS sQuantity
        ,Sales.unitPrice AS sPrice
        ,Sales.freight AS sFreight
    FROM tblPurchases AS Purchases
    LEFT OUTER JOIN tblSalesDetail AS Sales
        ON Purchases.productID = Sales.productNo

    UNION

    SELECT Purchases.productID AS productID
        ,Sales.productNo AS productNo
        ,Purchases.quantity AS pQuantity
        ,Purchases.price AS pPrice
        ,Purchases.freight AS pFreight
        ,Sales.quantity AS sQuantity
        ,Sales.unitPrice AS sPrice
        ,Sales.freight AS sFreight
    FROM tblSalesDetail AS Sales
    LEFT OUTER JOIN tblPurchases AS Purchases
        ON Purchases.productID = Sales.productNo
) AS FullOuterJoin
GROUP BY productID
    ,productNo

FULL OUTER JOIN(通过 UNION ALL 与两个 LEFT OUTER JOINS 完成，然后组合在一起)将在匹配项存在时关联它们，但提供两个表中的所有现有记录，即使一侧与另一侧不匹配，反之亦然。

希望这有帮助。