检索对象中组合票数最高的集合 [英] Retrieving sets with highest number of combined votes among objects

问题描述

我有3个与此问题相关的表格-排名集，项目和投票.排名集本质上是项目所在的类别，例如收藏夹运动".项目是实际正在投票的项目，例如棒球".投票是投票本身的日志.我想做的是在页面上显示25个最活跃的排名集.这是表格本身的样子:

I have three tables relevant to this problem - rankset, item, and vote. Rankset is essentially the category the item is placed in, such as "Favorite sport". Item is what's actually being voted on, such as "Baseball". Vote is the log of the vote itself. What I want to do is display the 25 most active ranksets on a page. Here's what the tables themselves look like:

CREATE  TABLE IF NOT EXISTS `rankset` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` TEXT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(128) NOT NULL ,
  `rankset` BIGINT NOT NULL ,
  `image` VARCHAR(45) NULL ,
  `description` VARCHAR(140) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `mydb`.`vote` (
  `id` BIGINT NOT NULL AUTO_INCREMENT ,
  `value` TINYINT NOT NULL ,
  `item` BIGINT NOT NULL ,
  `user` BIGINT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

这是我到目前为止尝试过的:

This is what I've tried so far:

SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes 
   FROM rankset, item, vote 
   WHERE rankset.id = item.rankset 
   AND vote.item = item.id ORDER BY votes DESC LIMIT 25

无论出于什么原因，我似乎都只能用它获得单个最受欢迎的排名.我也尝试过:

For whatever reason, I seem to only be able to get the single most popular rankset with that. I've also tried this:

SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes 
   FROM rankset, vote, item 
   WHERE item.rankset = rankset.id 
   GROUP BY rankset ORDER BY votes DESC LIMIT 25

但是，这似乎完全忽略了"ORDER BY"部分.正确的方法是什么?

But that seems to ignore the "ORDER BY" part completely. What would be the correct way to go about this?

这是小提琴: http://sqlfiddle.com/#!2/b57ac

推荐答案

您的查询几乎正确.在第一个中，您应该只添加"GROUP BY rankset".尝试:

Your queries are nearly right. In first one you should just add 'GROUP BY rankset'. Try that:

    SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes
      FROM rankset, item, vote 
    WHERE rankset.id = item.rankset 
      AND vote.item = item.id 
    GROUP by rankset.id 
    ORDER BY votes DESC 
    LIMIT 25;

这是小提琴: http://sqlfiddle.com/#!2/fe315/9 .

更新: 如果某个排名集没有任何投票的情况:

UPDATE: The case if some rankset doesn't have any votes:

    SELECT rankset.*, COALESCE(COUNT(vote.id), 0) AS votes
       FROM rankset, item
       LEFT JOIN vote ON (vote.item = item.id)
       WHERE rankset.id = item.rankset 
       GROUP by rankset.id 
       ORDER BY votes DESC 
       LIMIT 25;

这篇关于检索对象中组合票数最高的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！