MySQL中的计数问题

Question

我试图解决一个问题，但我需要帮助:)

实际上，这个查询是有效的，我在最后一列中有所有销售额的总和：

SELECT DISTINCT epps.SKU, epc.name , 
                 COUNT(*) FROM ERP_CUSTOMER_ORDER eco
                INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 ecoi2 ON ecoi2.ERP_CUSTOMER_ORDER_index = eco.`index`
                INNER JOIN ERP_PROD_PRODUCT_SIZES epps ON ecoi2.prod_index = epps.ID
                INNER JOIN ERP_PROD_CATALOG epc ON epps.`SKUf ID`= epc.`index`

                        WHERE (eco.state = "processing" OR eco.state= "complete")
                            AND eco.created_at > DATE_ADD("2015-11-06",INTERVAL 12 HOUR)
                            AND eco.created_at < DATE_ADD("2015-12-06",INTERVAL 12 HOUR)
                            AND epc.attribute_set = "Contact Lenses"
                            AND eco.order_location = "KL"

GROUP BY epps.SKU
ORDER BY COUNT(*) DESC

​

​

我想知道每30天售出的产品数量。我还想知道上个月售出的产品数量。然而，它并不起作用。这是我的新查询：

我想比较两个不同月份的销售情况

SELECT DISTINCT epps.SKU, epc.name , 
                (SELECT COUNT(*) FROM ERP_CUSTOMER_ORDER eco
                INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 ecoi2 ON ecoi2.ERP_CUSTOMER_ORDER_index = eco.`index`
                INNER JOIN ERP_PROD_PRODUCT_SIZES epps ON ecoi2.prod_index = epps.ID
                INNER JOIN ERP_PROD_CATALOG epc ON epps.`SKUf ID`= epc.`index`

                        WHERE (eco.state = "processing" OR eco.state= "complete")
                            AND eco.created_at > DATE_ADD("2015-11-06",INTERVAL 12 HOUR)
                            AND eco.created_at < DATE_ADD("2015-12-06",INTERVAL 12 HOUR)
                            AND epc.attribute_set = "Contact Lenses"
                            AND eco.order_location = "KL"
                            ),


                            (SELECT COUNT(*) FROM ERP_CUSTOMER_ORDER eco
                INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 ecoi2 ON ecoi2.ERP_CUSTOMER_ORDER_index = eco.`index`
                INNER JOIN ERP_PROD_PRODUCT_SIZES epps ON ecoi2.prod_index = epps.ID
                INNER JOIN ERP_PROD_CATALOG epc ON epps.`SKUf ID`= epc.`index`

                        WHERE (eco.state = "processing" OR eco.state= "complete")
                            AND eco.created_at > DATE_ADD("2015-10-06",INTERVAL 12 HOUR)
                            AND eco.created_at < DATE_ADD("2015-11-06",INTERVAL 12 HOUR)
                            AND epc.attribute_set = "Contact Lenses"
                            AND eco.order_location = "KL"
                            )
FROM ERP_CUSTOMER_ORDER eco
                INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 ecoi2 ON ecoi2.ERP_CUSTOMER_ORDER_index = eco.`index`
                INNER JOIN ERP_PROD_PRODUCT_SIZES epps ON ecoi2.prod_index = epps.ID
                INNER JOIN ERP_PROD_CATALOG epc ON epps.`SKUf ID`= epc.`index`                                          
GROUP BY epps.SKU 
ORDER BY COUNT(*) DESC

​

​

我使用了大致相同的框架，但我不能得到与以前相同的结果。

非常感谢!纪劳姆

Answer 1

性能是您在这里的主要关注点吗？如果不是这样，使用两个子查询并连接它们可能会很好地工作。如下所示(未经过测试)：

SELECT * from
(
SELECT DISTINCT epps.SKU, epc.name , 
                 COUNT(*) FROM ERP_CUSTOMER_ORDER eco
                INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 ecoi2 ON ecoi2.ERP_CUSTOMER_ORDER_index = eco.`index`
                INNER JOIN ERP_PROD_PRODUCT_SIZES epps ON ecoi2.prod_index = epps.ID
                INNER JOIN ERP_PROD_CATALOG epc ON epps.`SKUf ID`= epc.`index`

                        WHERE (eco.state = "processing" OR eco.state= "complete")
                            AND eco.created_at > DATE_ADD("2015-11-06",INTERVAL 12 HOUR)
                            AND eco.created_at < DATE_ADD("2015-12-06",INTERVAL 12 HOUR)
                            AND epc.attribute_set = "Contact Lenses"
                            AND eco.order_location = "KL"

GROUP BY epps.SKU
ORDER BY COUNT(*) DESC
) a
inner join 
(
SELECT DISTINCT epps.SKU, epc.name , 
                 COUNT(*) FROM ERP_CUSTOMER_ORDER eco
                INNER JOIN ERP_CUSTOMER_ORDER_ITEMS_2 ecoi2 ON ecoi2.ERP_CUSTOMER_ORDER_index = eco.`index`
                INNER JOIN ERP_PROD_PRODUCT_SIZES epps ON ecoi2.prod_index = epps.ID
                INNER JOIN ERP_PROD_CATALOG epc ON epps.`SKUf ID`= epc.`index`

                        WHERE (eco.state = "processing" OR eco.state= "complete")
                            AND eco.created_at > DATE_ADD("2015-10-06",INTERVAL 12 HOUR)
                            AND eco.created_at < DATE_ADD("2015-11-06",INTERVAL 12 HOUR)
                            AND epc.attribute_set = "Contact Lenses"
                            AND eco.order_location = "KL"

GROUP BY epps.SKU
ORDER BY COUNT(*) DESC
) b
on a.SKU = b.SKU and a.name = b.name

请注意，如果您的数据中没有零值，则此查询将不起作用(例如，对于没有销售任何镜片的月份，如果您没有行，则由于内部连接，此查询将不会返回任何月份的结果)。