我有表存储产品价格信息,表看起来类似,(不是主键)
no name price date 1 paper 1.99 3-23 2 paper 2.99 5-25 3 paper 1.99 5-29 4 orange 4.56 4-23 5 apple 3.43 3-11
现在我想选择“name”字段在表格中出现多次的所有行.基本上,我希望我的查询返回前三行.
我试过了：
SELECT * FROM product_price_info GROUP BY name HAVING COUNT(*) > 1
但我得到一个错误说：
column “product_price_info.no” must appear in the GROUP BY clause or be used in an aggregate function
解决方法
SELECT *
FROM product_price_info
WHERE name IN (SELECT name
FROM product_price_info
GROUP BY name HAVING COUNT(*) > 1)
