我正在尝试运行如下查询:
SELECT cc.slug AS cc_slug
, cp.slug AS cp_slug
, COUNT(cp.id)
FROM catalog_categories AS cc
LEFT
JOIN catalog_products cp
ON cc.id = cp.category_id
GROUP
BY cc.id
, cp.id
并期望获得每个类别的相关产品数量,但我只检索"0"和"1"而不是正确的计数。它是由第二个分组group by ... cp.id
引起的,但无法删除,因为它已启用ONLY_FULL_GROUP_BY。因此,例如,如果我运行查询(不按连接表分组):
SELECT COUNT(cp.id)
FROM catalog_categories AS cc
LEFT
JOIN catalog_products cp
ON cc.id = cp.category_id
GROUP
BY cc.id
我得到了正确的结果,但问题是我需要 SELECT 中的所有这些字段和别名。
有什么办法可以解决这个问题吗?
您可以将
cp.id
放在聚合函数中,如下所示:
SELECT COUNT(cp.id), MAX(cp.id)
FROM catalog_categories AS cc
LEFT JOIN catalog_products cp ON cc.id = cp.category_id
GROUP BY cc.id
这样,您可以选择每cc.id
的最大cp.id
。
我认为这就是你想要的:
SELECT cc.slug AS cc_slug, cp.slug AS cp_slug, COUNT(cp.id)
FROM catalog_categories cc LEFT JOIN
catalog_products cp
ON cc.id = cp.category_id
GROUP BY cc.slug, cp.slug;
非聚合列应位于GROUP BY
中。
编辑:
这是你想要的吗?
SELECT cc.slug AS cc_slug, COUNT(cp.id)
FROM catalog_categories cc LEFT JOIN
catalog_products cp
ON cc.id = cp.category_id
GROUP BY cc.slug;