问题描述
我有三个表格,我需要在搜索,电影,评论和投票使用。我想对Movie.Title和Review.Subject使用LIKE函数,并按每个匹配的最多投票数量排序。
在Votes表中有一个ReviewID,UserID,IsGood。每次用户投票时,都会使用MovieID,UserID和IsGood的1或0进行插入,1表示好0表示不好。
有0个好的和坏的投票,或5个好和3个坏等。我想按照以下顺序显示结果:
/ 3Bad
strong> Review 3 - 0Good / 0Bad
匹配最顶端的投票数最多,底部投票数最少。 p>
这是我写的mysql查询,显然是错误的,但希望有人可以帮助我:
mysql_query(
SELECT m.Title,r.Subject,v.ReviewID FROM Movies m
LEFT JOIN评论r
ON m.ID = r.MovieID
INNER JOIN投票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)或die(mysql_error());
要从一组连接的表行获得总和或良好的投票和错误投票,您需要将相似的行组合在一起。
在应该给您所需的结果。
mysql_query(
SELECT m.Title,r.Subject ,v.TipID,sum(v.IsGood)as IsGood,sum(v.isBad)as isBad FROM Movies m
LEFT JOIN评论r
ON m.ID = r.MovieID
LEFT JOIN投票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)或die mysql_error());
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());
这篇关于如何使用LIKE的mysql搜索与JOIN和ORDER BY的计数最多的行/投票在投票表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!