Mysql计数结果在 "where"子句中

Question

我在 mysql where 子句中遇到了一个小问题。这是查询:

SELECT u.id user
     , p.id product_purchased
     , p.name product_name
     , pl.store_id store
     , COUNT(*) occurrences
     , total_spent
     , total_product_purchased
     , pl.registration 
  FROM purchases_log pl 
  JOIN user u 
    ON pl.user_id = u.id 
  JOIN product p 
    ON pl.product_id = p.id
  JOIN 
     ( SELECT user_id
            , SUM(price) total_spent
            , COUNT(product_id) total_product_purchased 
         FROM purchases_log pl 
        GROUP 
           BY user_id
     ) t1 
    ON u.id = t1.user_id
 WHERE pl.store_id IN (1,2,3) 
   AND occurrences > 1 
 GROUP 
    BY user
     , product_name
 ORDER 
    BY u.id ASC
     , pl.registration ASC;

这是输出错误:

错误代码:1054。 “where 子句”中的未知列“出现”0.067 秒

我已经尝试将 AS 分配给出现的事件或使用 pl。那么，有人可以解释一下如何在 where 子句中正确定义 count 函数的结果吗？

Answer 1

您需要使用 HAVING 而不是 COUNT，因为在 WHERE 子句之后应用分组依据，因此，它不会知道任何分组/聚合列，例如/:

SELECT u.id user,p.id product_purchased, p.name product_name,  pl.store_id store, COUNT(*) AS occurrences, total_spent, total_product_purchased, pl.registration 
FROM purchases_log pl 
JOIN user u ON pl.user_id=u.id 
JOIN product p ON pl.product_id=p.id
JOIN (SELECT user_id, SUM(price) AS total_spent,COUNT(product_id) AS total_product_purchased FROM purchases_log pl GROUP BY user_id) t1 ON u.id=t1.user_id
WHERE pl.store_id IN (1,2,3) 
GROUP BY user, product_name
HAVING COUNT(*) > 1
ORDER BY u.id ASC, pl.registration ASC;

更新

• 如果用户关联了多个产品，则最好在 GROUP BY 中添加所有非聚合列，以获取用户和产品的所有组合。当前查询不会返回所有组合。
• 如 @strawberry 建议的那样，为了进一步优化，您可以运行 EXPLAIN 并查看使用了哪些索引以及是否需要创建任何新索引。