检索对象之间组合投票数最高的集

Question

我有三个与此问题相关的表格 - 排名集、项目和投票。排名集本质上是项目所在的类别，例如"最喜欢的运动"。项目是实际投票的内容，例如"棒球"。投票是投票本身的日志。我想做的是在页面上显示 25 个最活跃的排名集。以下是表本身的外观：

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

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

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

无论出于何种原因，我似乎只能获得一个最受欢迎的排名集。我也试过这个：

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"部分。正确的方法是什么？

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

Answer 1

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

    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。

更新：如果某些等级集没有任何投票的情况：

    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;