mysql - MySQL中有多个GROUP BY和COUNT

我的Sql结构：

CREATE TABLE collection (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id int(11) DEFAULT NULL,
  `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (id),
  KEY user_id (user_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE collection_link (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  collection_id int(11) DEFAULT NULL,
  configitem_id bigint(20) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY IDX_7CDBB51F514956FD (collection_id),
  KEY IDX_7CDBB51F9D3DD91F (configitem_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE configitem (
  id bigint(20) NOT NULL,
  PRIMARY KEY (id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE user_account (
  id int(11) NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


ALTER TABLE collection
  ADD CONSTRAINT FK_FC4D6532A76ED395 FOREIGN KEY (user_id) REFERENCES user_account (id),

ALTER TABLE collection_link
  ADD CONSTRAINT FK_7CDBB51F514956FD FOREIGN KEY (collection_id) REFERENCES collection (id),
  ADD CONSTRAINT FK_7CDBB51F9D3DD91F FOREIGN KEY (configitem_id) REFERENCES configitem (id);

其次，一个user_account可以在集合中添加许多配置，也可以根据需要在集合中添加相同的项目。

这样，我需要查找添加到集合中的顶级配置项，并避免用户在其集合中添加重复项。如果一个用户在一个集合中有5个相同的配置项，也只能算一个...这就是我的问题。

接着就，随即：


  SELECT ID，SUM（num）FROM（
      SELECT l.configitem_id作为id，COUNT（DISTINCT l.configitem_id）作为num FROM collection_link l LEFT JOIN collection c9_ ON l.collection_id
  = c9_.id左加入user_account u2_ ON c9_.user_id = u2_.id WHERE l.configitem_id = 1121 GROUP BY u2_.id，l.configitem_id
      ）作为cmpt;


我可以收到configitem 1121的确切计数，但是如何应用于所有？

因为我所有的测试都失败了...

这有效并需要添加前25名：

SELECT DISTINCT c2_.id AS id_0, count(c1_.id) AS sclr_1
FROM collection_link c1_
LEFT JOIN configitem c2_ ON c1_.configitem_id = c2_.id
LEFT JOIN collection c8_ ON c1_.collection_id = c8_.id
LEFT JOIN user_account u9_ ON c8_.user_id = u9_.id
GROUP BY c2_.id
ORDER BY sclr_1 DESC LIMIT 25;

但需要重复。

最佳答案

如果我理解正确，则需要在计数之前按用户和配置项进行汇总。或者，只需执行count(distinct)：

SELECT c2_.id AS id_0,
       COUNT(DISTINCT u9_.id) AS sclr_1
FROM collection_link c1_ LEFT JOIN
     configitem c2_
     ON c1_.configitem_id = c2_.id LEFT JOIN
     collection c8_
     ON c1_.collection_id = c8_.id LEFT JOIN
     user_account u9_
     ON c8_.user_id = u9_.id
GROUP BY c2_.id
ORDER BY sclr_1 DESC
LIMIT 25;

请注意，此版本的查询不需要连接到用户表：

SELECT c2_.id AS id_0,
       COUNT(DISTINCT c8_.user_id) AS sclr_1
FROM collection_link c1_ LEFT JOIN
     configitem c2_
     ON c1_.configitem_id = c2_.id LEFT JOIN
     collection c8_
     ON c1_.collection_id = c8_.id
GROUP BY c2_.id
ORDER BY sclr_1 DESC
LIMIT 25;

关于mysql - MySQL中有多个GROUP BY和COUNT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35827401/