我正试图统计一件物品的购买次数。然而,我的查询中也有一个类别表,这当然会导致错误的数据。如何在一个表中计算所需的行,然后只得到这些结果?
MySQL查询
select
`inventory`.`on_sale`,
`inventory`.`id`,
`inventory`.`sku`,
`inventory`.`name`,
`inventory`.`price`,
`inventory_categories`.`category`,
`inventory`.`stock_quantity`,
COUNT(orders_product.sku) as skuCount
from
`inventory`
left join
`orders_product` ON `orders_product`.`sku` = `inventory`.`sku`
left join
`inventory_categories` ON `inventory`.`sku` = `inventory_categories`.`sku`
group by `inventory_categories`.`category`
order by `skuCount` desc
limit 10000
很难显示我所有的记录,但基本上是这样的:
orders_product.sku = ORDER1234 & ORDER1234 & ORDER891010
inventory_categories.(id)category = (cat1)ORDER1234 & (cat1a)ORDER1234 & (cat1b)ORDER1234 & (cat2)ORDER891010
所以在我的表中,它显示skuCount=5&2,而不是所需的2&1。如果我没有带有该库存.id的订单,那么它会显示0,这是正确的,但一旦订单通过,它就会计算该订单加上等于该库存单位的所有类别。
如果我从联接语句中删除inventory_categories,它就不会有任何问题。但是,出于记录目的,查询中需要它。
下面的查询如何:
SELECT i.on_sale, i.id, i.sku, i.name, i.price, ic.category, i.stock_quantity,
(
SELECT COUNT(*)
FROM orders_product op
WHERE op.sku = i.sku
) AS skuCount
FROM inventory i
LEFT JOIN inventory_categories ic ON i.sku = ic.sku
ORDER BY skuCount DESC
LIMIT 10000
SELECT A.on_sale, a.id, a.sku,a.name,a.price, a.stock_Quantity, skucount, count(ic.category) as CatCount
FROM
( SELECT
`inventory`.`on_sale`,
`inventory`.`id`,
`inventory`.`sku`,
`inventory`.`name`,
`inventory`.`price`,
`inventory`.`stock_quantity`,
COUNT(orders_product.sku) as skuCount
FROM `inventory`
LEFT JOIN `orders_product`
ON `orders_product`.`sku` = `inventory`.`sku`
GROUP BY `inventory`.`on_sale`, `inventory`.`id`,
`inventory`.`sku`, `inventory`.`name`,
`inventory`.`price`, `inventory`.`stock_quantity`) A
LEFT JOIN `inventory_categories` IC
ON `inventory`.`sku` = `inventory_categories`.`sku`
GROUP BY A.on_sale, a.id, a.sku,a.name,a.price, a.stock_Quantity, skucount
OREDER BY `skuCount` desc
LIMIT 10000
您必须首先获得一个包含SKU计数的结果集,然后加入表。