怎么在mysql加入算法_MySQL加入问题

我试图返回产品表中未被当前用户添加的产品的结果,并且当前用户尚未审核,然后按照评论最少的产品排序显示那些没有的产品.

> tblUsers：

UserID

1

2

3

> tblProducts：

ProductID UserID (created) NumberReviews

--------- ---------------- -------------

1 1 1

2 1 0

3 2 1

4 1 2

5 2 0

> tblReviews：

ReviewID UserID(reviewed) ProductID

-------- ---------------- ---------

1 2 4

2 1 3

3 3 4

4 3 1

所以对于2的当前用户我想要返回

Product ID

----------

2

1

我尝试了许多不同的左连接但无济于事.有任何想法吗？

最佳答案

SELECT p.*

, COUNT(r.ProductID) AS reviews

FROM tblProducts AS p

LEFT JOIN tblReviews AS r

ON r.ProductID = p.ProductID

WHERE UserID <> @currentUser

AND NOT EXISTS

( SELECT *

FROM tblReviews ru

WHERE ru.ProductID = p.ProductID

AND ru.UserID = @currentUser

)

GROUP BY p.ProductID

ORDER BY reviews ASC

如果您不想计算但使用NumberReviews字段进行排序,则更简单：

SELECT p.*

FROM tblProducts AS p

WHERE UserID <> @currentUser

AND NOT EXISTS

( SELECT *

FROM tblReviews ru

WHERE ru.ProductID = p.ProductID

AND ru.UserID = @currentUser

)

ORDER BY NumberReviews ASC