mysql - 为什么此查询失败？

Question

当我运行这个查询时:

SELECT m.tag_id, m.product_id, count(m.product_id) as dups
FROM product_tag_map m
INNER JOIN category_tag_map s ON s.tag_id = m.tag_id
INNER JOIN cart_product p ON m.product_id = p.product_id
WHERE m.product_id = p.product_id
AND s.category_id =508
AND s.include =1
AND dups = (SELECT count(s.category_id) as cats FROM category_tag_map s WHERE s.category_id =508)
GROUP BY m.product_id
ORDER BY dups DESC

我收到这个错误:

1054 - Unknown column 'dups' in 'where clause'

如果我从 where 子句中取出重复项，我会得到:

tag_id  product_id  dups
2   7038    2
2   1294    1

我想我不能在 where 子句中使用 dups 字段，因为该字段是动态生成的。那么我该如何做我想做的事情呢？

Answer 1

在过滤聚合时，您需要使用 Having。 MySQL 实际上允许您在 having 子句中引用列别名(大多数其他 RDBMS 不允许这样做)

SELECT m.tag_id, m.product_id, count(m.product_id) as dups
FROM product_tag_map m
INNER JOIN category_tag_map s ON s.tag_id = m.tag_id
INNER JOIN cart_product p ON m.product_id = p.product_id
WHERE m.product_id = p.product_id
AND s.category_id =508
AND s.include =1
GROUP BY m.product_id
HAVING dups = (SELECT count(s.category_id) as cats FROM category_tag_map s WHERE s.category_id =508)
ORDER BY dups DESC