我在product_id及其价格的products表中有这些记录
product_id | 价格 |
---|---|
1 | 150 |
1 | 190 |
2 | 20 |
2 | 12 |
3 | 123 |
4 | 513 |
5 | 157 |
5 | 147 |
WITH AverageCTE AS
(
SELECT product_id, AVG(avg_price) as avg_price
FROM products
GROUP BY product_id
)
SELECT p.product_id, price, avg_price
FROM product p JOIN
(SELECT * FROM AverageCTE ORDER BY avg_price DESC LIMIT 3) a
on p.product_id = a.product_id
ORDER BY avg_price DESC