mysql like order by,如何使用LIKE的mysql搜索与JOIN和ORDER BY的计数最多的行/投票在投票表？...

I have three tables I need to use in a search, Movies, Reviews, and Votes. I want to use the LIKE function for Movie.Title and Review.Subject and order them by the amount of the most votes for each match.

In the Votes table there is a ReviewID, UserID, IsGood. Every time a user votes, an insert is done with the MovieID, UserID, and 1 or 0 for the IsGood, 1 meaning good 0 meaning bad.

So one review may have 0 good and bad votes, or 5 good and 3 bad, etc. I would like to show the results in the following order:

Review 1 - 10Good / 3Bad

Review 2 - 4Good / 3Bad

Review 3 - 0Good / 0Bad

The matches with the most good votes at top, the ones with the most bad votes at the bottom.

This is the mysql query I wrote up and is obviously wrong, but hoping someone can help me out:

mysql_query("

SELECT m.Title, r.Subject, v.ReviewID FROM Movies m

LEFT JOIN Reviews r

ON m.ID=r.MovieID

INNER JOIN Votes v

ON r.ID=v.ReviewID

WHERE (m.Title LIKE '%" . $search . "%'

OR r.Subject LIKE '%" . $search . "%')

ORDER BY MAX(COUNT(v.IsGood='1')) LIMIT 10")or die(mysql_error());

解决方案

Here is a fuller answer.

To get the sum or good votes and bad votes from a set of joined table rows, you need to group the like rows together.

Below should give you the desired result.

mysql_query("

SELECT m.Title, r.Subject, v.TipID, sum(v.IsGood) as IsGood, sum(v.isBad) as isBad FROM Movies m

LEFT JOIN Reviews r

ON m.ID=r.MovieID

LEFT JOIN Votes v

ON r.ID=v.ReviewID

WHERE (m.Title LIKE '%" . $search . "%'

OR r.Subject LIKE '%" . $search . "%')

GROUP BY m.Title, r.Subject, v.TipID

ORDER BY sum(v.IsGood) desc, sum(v.isBad) asc LIMIT 10")or die(mysql_error());