我有表存储产品价格信息,表看起来类似,(不是主键)
- 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)
