mysql - 根据条件检索值

Question

请查看以下查询。

SELECT `p_id`, COUNT(`p_id`) AS OverlapWords, `UniqueWordCount`, 
(COUNT(`p_id`)/`UniqueWordCount`) AS SimScore FROM `key_uniqueword` WHERE `word` IN 
('stand','on') GROUP BY (`p_id`) LIMIT 500

我需要将 SimScore 转换为百分比，并获取 SimScore 小于 90% 的所有记录。我怎样才能在 SQL 中做到这一点？上次我这样做时，它给我抛出了一个错误，类似于 Invalid use of Group function

Answer 1

你试过这个吗？

SELECT `p_id`, COUNT(`p_id`) AS OverlapWords, `UniqueWordCount`, 
       (COUNT(`p_id`)/`UniqueWordCount`) AS SimScore
FROM `key_uniqueword`
WHERE `word` IN ('stand','on')
GROUP BY (`p_id`)
HAVING SimScore < 0.9
LIMIT 500;

聚合函数结果的条件属于 having 子句，而不是 where 子句。