Mysql сравнить продажи/покупку в одном запросе

У нас есть проприетарная система продаж, которую мы использовали некоторое время. Недавно мы добавили сторону "покупки", чтобы сравнить средние цены покупки/продажи для соответствия товарам, а также посмотреть позицию инвентаря.

В MySQL у меня есть 2 таблицы: tblPurchases и tblSalesDetail.

Каждый из них имеет идентификаторы товаров, количества, цены и грузов (все номера).

Я хочу запросить идентификатор продукта:

[идентификатор товара] [общая сумма покупки] [средняя цена покупки] [средняя сумма покупки] [общая сумма продажи] [средняя цена продажи] [распродажа]

Моя текущая инструкция 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

но он работает только тогда, когда есть соответствующий идентификатор продукта с обеих сторон. Я хочу, чтобы он также отображал продажи для идентификатора продукта, для которого нет соответствующих идентификаторов продуктов покупки.

Возможно ли это с помощью одного запроса?

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

отредактированный

новый запрос выглядит так:

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
2 ответа

Это связано с тем, что вы делаете 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 s.

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 JOIN S, затем GROUP вместе) будет относить совпадения, когда они существуют, но предоставить все существующие записи из обеих таблиц, даже если одна сторона не соответствует другой или наоборот.

Надеюсь это поможет.


Проблема в том, что вам нужно full outer join, но MySQL его не поддерживает. Тем не менее, вы можете сделать в основном одно и то же с union all и агрегацией. В основном это выглядит так:

select ps.productID, 
 sum(pquantity) as pQuantity, avg(pPrice) as pPrice, avg(pFreight) as pFreight,
 sum(squantity) as sQuantity, avg(sPrice) as sPrice, avg(sFreight) as sFreight
from ((select productid,
 quantity as pQuantity, price as pPrice, freight as pFreight,
 NULL as sQuantity, NULL as sPrice, NULL as sfreight
 from tblPurchases
 ) union all
 (select productno, NULL as pQuantity
 quantity as sQuantity, price as sPrice, freight as sFreight,
 from tblSalesDetail
 )
 ) ps
group by ps.productID;

licensed under cc by-sa 3.0 with attribution.