douzhang8144
2013-08-06 12:11 阅读 126
已采纳

Mysql一对多的关系。 按第二个表中的行数排序

I have created a voting system in php and mysql. When a user votes on an id, a record is inserted in "votes" referencing the FK media_id. When I then display the entries I use this query to get the number of votes for each entry:

$sql = "SELECT COUNT(*) FROM insta_votes WHERE media_id ='".$mediaid."'";
if ($res = $db->query($sql)) {
    return $res->fetchColumn();
}
return 0;

This works fine, but I want to be able to sort the results by the number of votes they have. Preferably using just one query. How can I achieve this?

The tables are structured like this:

votes table
+-----------+--------------+------+-----+-------------------+----------------+
| Field     | Type         | Null | Key | Default           | Extra          |
+-----------+--------------+------+-----+-------------------+----------------+
| id        | int(11)      | NO   | PRI | NULL              | auto_increment |
| media_id  | varchar(255) | NO   |     | NULL              |                |
| ip        | varchar(20)  | NO   |     | NULL              |                |
| c_time    | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| sessionid | varchar(30)  | NO   |     | NULL              |                |
+-----------+--------------+------+-----+-------------------+----------------+

entries table
+---------------+--------------+------+-----+-------------------+-------+
| Field         | Type         | Null | Key | Default           | Extra |
+---------------+--------------+------+-----+-------------------+-------+
| page_id       | int(11)      | NO   | MUL | NULL              |       |
| media_id      | varchar(255) | NO   | PRI | NULL              |       |
| url           | varchar(255) | NO   |     | NULL              |       |
| c_time        | datetime     | NO   |     | NULL              |       |
| likes         | int(11)      | YES  |     | NULL              |       |
| deleted       | tinyint(1)   | NO   |     | 0                 |       |
| inserted_time | timestamp    | YES  |     | CURRENT_TIMESTAMP |       |
| numReports    | int(11)      | NO   |     | 0                 |       |
+---------------+--------------+------+-----+-------------------+-------+

Thank you!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • 已采纳
    dongpu5600 dongpu5600 2013-08-06 12:18

    If I understand the tables correctly (and I may not), each entries row may reference multiple votes rows. In that case, the query you need will go something like this:

    SELECT
      entries.page_id,
      COUNT(*) AS VoteCount
    FROM entries
    INNER JOIN votes ON entries.media_id = votes.media_id
    GROUP BY entries.page_id
    ORDER BY VoteCount
    

    If you add additional entries columns to the SELECT list, be sure to add them to the GROUP BY list as well.


    Addendum: @JuanPabloCalifano pointed out, correctly, that this query won't include entries with zero votes. Here's how to include them:

    SELECT
      entries.page_id,
      COALESCE(COUNT(votes.id), 0) AS VoteCount
    FROM entries
    LEFT JOIN votes ON entries.media_id = votes.media_id
    GROUP BY entries.page_id
    ORDER BY VoteCount
    
    点赞 评论 复制链接分享
  • duanfei7508 duanfei7508 2013-08-06 12:16
    SELECT COUNT(*) as CNT, `media_id` FROM `insta_votes` GROUP BY `media_id` order by 1;
    
    点赞 评论 复制链接分享
  • doupai8095 doupai8095 2013-08-06 12:17
    SELECT COUNT(*), media_id FROM insta_votes 
    GROUP BY media_id 
    ORDER BY COUNT(*);"
    
    点赞 评论 复制链接分享

相关推荐