如何使用LIKE的mysql搜索与JOIN和ORDER BY的计数最多的行/投票在投票表？ [英] How to use LIKE for mysql search with JOIN and ORDER BY the count of most rows/votes in the vote table?

问题描述

我有三个表格，我需要在搜索，电影，评论和投票使用。我想对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的计数最多的行/投票在投票表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！