您可能有具有相同最大值的重复项。如果是：

select t.product
from t join
     (select product, max(price) as maxprice
      from t
      group by product
     ) tt
     on t.product = tt.product
where t.price < t.maxprice
group by t.product;

如果最大值没有重复项，则只需从计数中减去1：

SELECT product, COUNT(*) - 1 AS counter FROM products GROUP BY product;

如果有重复的，你想把它们都排除在外：

SELECT p.product, COUNT(*) AS counter 
FROM products p 
WHERE p.value < (SELECT MAX(value) FROM products WHERE product = p.product) 
GROUP BY p.product;

或者如果要返回零：

SELECT 
  p.product, 
  COUNT(*) - (
    SELECT COUNT(*) 
    FROM products 
    WHERE 
      product = p.product 
      AND 
      value = (
        SELECT MAX(value) FROM products WHERE product = p.product
      )
  ) AS counter 
FROM products p 
GROUP BY p.product;