mysql - MySQL 中的多个 GROUP BY 和 COUNT

Question

我的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 as id, COUNT(DISTINCT l.configitem_id) as num FROM collection_link l LEFT JOIN collection c9_ ON l.collection_id = c9_.id LEFT JOIN user_account u2_ ON c9_.user_id = u2_.id WHERE l.configitem_id = 1121 GROUP BY u2_.id, l.configitem_id ) as 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;

但需要重复。

Answer 1

如果我理解正确，您需要在计数之前按用户和配置项进行聚合。或者，只需执行 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;