我有桌子材料:
- id: int
- Product: varchar (100)
- Content: text
- price: double
我想按最终价格显示按产品分组的材料清单:
SELECT * FROM materiel GROUP BY produit ORDER BY id
但在我看来,一组获得第一名的产品
13, product1, dell, 25
14, product2, hp, 4
17, product3, acer, 15
不使用分组依据的查询
13, product1, dell, 25
14, product2, hp, 4
16, product1, acer, 35
17, product3, acer, 15
我想要这个节目
14, product2, hp, 4
16, product1, acer, 35
17, product3, acer, 15
您可以使用IN()
语句:
SELECT * FROM product p
WHERE p.product,p.id IN(SELECT p1.product,max(p1.id)
FROM product p1
GROUP BY p1.product)
或一个JOIN
:
SELECT p.* FROM product p
INNER JOIN (SELECT p1.product,max(p1.id) as max_id
FROM product p1
GROUP BY p1.product) p1
ON(p.id = p1.max_id and p.product = p1.product)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句