mysql - SQL - 如何选择最受喜欢的产品？

Question

我有两张 table

Product(id, ..., other attribute...)

与

有关

UserLikeProduct(id, product_id, like_at, user_id)

如何选择最受好评的产品？

Answer 1

首先，您需要构建一个按从高到低排序的用户喜欢结果

select product_id,count(*) as TotLikes
from UserLikeProduct
group by Product_id

现在将其与产品表连接起来

select p.Product_id,tl.TotLikes * 
from Products p
join *
(     select product_id,count(*) as TotLikes
    from UserLikeProduct
    group by Product_id

) tl on tl.Product_id=p.Product_id
order by 2 DESC