mysql - 查询以获取用户最近和最高的事件

Question

我的表结构如下:

CREATE TABLE `scores` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `nick` VARCHAR(32) NOT NULL,
    `count` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
    `messages` SMALLINT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00000',
    `dated` DATE NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `nick_dated` (`nick`, `dated`),
    INDEX `nick` (`nick`),
    INDEX `count` (`count`),
    INDEX `messages` (`messages`)
)
COMMENT='Chat scores'
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

我希望编写一个查询，以便对于用户输入的 nick 值，我可以生成用户的:

• 总数
• 平均计数
• 最近一天的计数
• 最高计数日期

我编写的查询是:

SELECT s.`nick` AS `nick`,
    s.`count` AS `recent`,
    t.`total` AS `total`,
    t.`avrg` AS `avrg`,
    MAX(ss.`count`) AS `max`,
    ss.dated
FROM (
    SELECT `nick`,
        SUM(`count`) AS `total`,
        AVG(`count`) AS `avrg`,
        MAX(`dated`) AS `dated` # For most recent activity
    FROM `scores`
    WHERE `nick` = 'hjpotter92'
) AS `t`
INNER JOIN scores s
    ON s.nick = t.nick
        AND s.dated = t.dated
INNER JOIN scores ss
    ON ss.nick = t.nick

我可以选择前三个必需值。但是我怎样才能获得最高事件的日期。这是 sqlfiddle 。正如您在 fiddle DDL 中看到的，第 24 行

INSERT INTO `scores` 
    (`count`, `nick`, `dated`) 
VALUES 
    (00052, 'hjpotter92', '2013-07-29');

计数最高的日期(由 MAX(ss.count) 正确获取为 52)是 2013-07-29，但我的选择查询返回了我 2013 年 7 月 26 日。

我哪里做错了？

Answer 1

您的查询是奇怪的聚合组合。这是一个有效的版本:

SELECT s.`nick` AS `nick`,
    srecent.`count` AS `recent`,
    t.`total` AS `total`,
    t.`avrg` AS `avrg`,
    t.maxcount,
    s.dated
FROM (SELECT `nick`,
             SUM(`count`) AS `total`,
             AVG(`count`) AS `avrg`,
             MAX(`dated`) AS `dated`, # For most recent activity
             max(count) as maxcount
      FROM `scores`
      WHERE `nick` = 'hjpotter92'
      group by nick
     )  `t` INNER JOIN
    scores s
    ON s.nick = t.nick AND
       s.count = t.maxcount join
    scores srecent
    on srecent.nick = t.nick and
       srecent.dated = t.dated;

您的原始查询的问题是 select 中的 max(count)。这将外部 select 转变为聚合查询——返回一行。但是，ss 上的联接返回多行，因此从该联接返回任意行。因此结果不一致。

此版本计算子查询中的各种聚合值。然后它连接回原始表以从这些日期获取其他信息。

制定查询的另一种方法是使用 substring_index(group_concat()) 技巧:

SELECT `nick`,
       substring_index(group_concat(count order by dated desc), ',', 1) as mostrecent,
       SUM(`count`) AS `total`,
       AVG(`count`) AS `avrg`,
       max(count) as maxcount,
       substring_index(group_concat(dated order by count desc), ',', 1) as dated
FROM `scores`
WHERE `nick` = 'hjpotter92'
group by nick;