MySQL SELECT按组最常出现

MySQL SELECT按组最常出现

SELECt t1.*FROM (SELECt tag, category, COUNT(*) AS count      FROM tags INNER JOIN stuff USING (id)      GROUP BY tag, category) t1LEFT OUTER JOIN      (SELECt tag, category, COUNT(*) AS count      FROM tags INNER JOIN stuff USING (id)      GROUP BY tag, category) t2  ON (t1.tag = t2.tag AND (t1.count < t2.count       OR t1.count = t2.count AND t1.category < t2.category))WHERe t2.tag IS NULLORDER BY t1.count DESC;

---

我同意这对于单个SQL查询来说实在太多了。

GROUP BY

子查询内部的任何使用都会使我畏缩。您可以通过使用视图使它 看起来 更简单：

CREATE VIEW count_per_category AS    SELECt tag, category, COUNT(*) AS count    FROM tags INNER JOIN stuff USING (id)    GROUP BY tag, category;SELECt t1.*FROM count_per_category t1LEFT OUTER JOIN count_per_category t2  ON (t1.tag = t2.tag AND (t1.count < t2.count       OR t1.count = t2.count AND t1.category < t2.category))WHERe t2.tag IS NULLORDER BY t1.count DESC;

但它基本上是在幕后做同样的工作。

您评论说，您可以在应用程序代码中轻松地执行类似的 *** 作。那你为什么不那样做呢？进行更简单的查询以获取每个类别的计数：

SELECt tag, category, COUNT(*) AS countFROM tags INNER JOIN stuff USING (id)GROUP BY tag, category;

并在应用程序代码中对结果进行排序。