mysql - 计算标准化表中的记录数

Question

看到的较旧问题

• Counting one table of records for matching records of another table
• MySQL Count matching records from multiple tables
• Count records from two tables grouped by one field

表架构

表条目包含2005-01-25

的数据

CREATE TABLE `entries` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ctg` VARCHAR(15) NOT NULL,
    `msg` VARCHAR(200) NOT NULL,
    `nick` VARCHAR(30) NOT NULL,
    `date` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `msg` (`msg`),
    INDEX `date` (`date`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

子表磁铁，其中包含来自2011-11-08的常规数据(可能有一些条目从之前开始)

CREATE TABLE `magnets` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `eid` INT(10) UNSIGNED NOT NULL,
    `tth` CHAR(39) NOT NULL,
    `size` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `nick` VARCHAR(30) NOT NULL DEFAULT 'hjpotter92',
    `date` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `eid_tth` (`eid`, `tth`),
    INDEX `entriedID` (`eid`),
    INDEX `tth_size` (`tth`, `size`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

问题

我想获取在任一表中输入的任何特定昵称(或用户)的条目总数。

entries 中的条目之一与 magnets 同时填充，并且 magnets 的后续条目可以来自相同的 nick 或不同。

我的代码

• 尝试 1

  SELECT `e`.id, COUNT(1), `e`.nick, `m`.nick
  FROM `entries` `e`
  INNER JOIN `magnets` `m`
      ON `m`.`eid` = `e`.id
  GROUP BY `e`.nick
  

• 尝试 2

  SELECT `e`.id, COUNT(1), `e`.nick
  FROM `entries` `e`
  GROUP BY `e`.nick
  UNION ALL
  SELECT `m`.eid, COUNT(1), `m`.nick
  FROM `magnets` `m`
  GROUP BY `m`.nick
  

第二次尝试是生成一些相关输出，但它包含两个表中出现的所有nick的双重条目。

此外，我不想对第一个查询中插入的那些条目/磁铁进行两次计数。这就是第二个 UNION 语句所做的事情。它接收两个表中的所有值。

SQL Fiddle 链接

Here 是 SQL Fiddle 的链接以及随机填充的条目。

我真的希望有人能指导我解决这个问题。如果有任何帮助，我将使用 PHP 来最终显示数据。因此，我最后的手段是在 PHP 中嵌套循环进行计数(我目前正在这样做)。

期望的输出

fiddle 上应该生成的输出应该是:

************************************************
**        Nick        |||        Count        **
************************************************
**        Nick1       |||         10          **
**        Nick2       |||          9          **
**        Nick3       |||          6          **
**        Nick4       |||         10          **
************************************************

Answer 1

可能有一种更有效的方法，但如果我理解正确的话，这是可行的:

SELECT SUM(cnt), nick FROM
(SELECT count(*) cnt, e.nick FROM entries e 
  LEFT JOIN magnets m ON (e.id=m.eid AND e.nick=m.nick) 
  WHERE eid IS NULL GROUP BY e.nick
UNION ALL
  SELECT count(*) cnt, nick FROM magnets m GROUP BY nick) u 
GROUP BY nick