统计属于 MySQL 中某个类别的记录

Question

我一直在与一些 SQL 作斗争，但似乎无法理解它。

我有两张表，一张包含类别列表，另一张包含我的所有文章。

我想做的是找出每个类别有多少篇文章。

这是我到目前为止的 SQL

SELECT DISTINCT COUNT( po.post_Cat_ID ) AS Occurances, ca.cat_Title
FROM Posts po, Categories ca
WHERE ca.cat_ID = LEFT( po.post_Cat_ID, 2 )

我使用 LEFT 的原因是只获取主要类别，因为我列出了以下类别...例如，

Science = 01
Medicine = 0101
Sport = 02

关于阿司匹林的帖子因此将 cat_ID 设为 0101。（LEFT 然后会修剪0101、0102、0103 等至 01)。基本上我对子类别不感兴趣。

提前感谢

---

结果

SELECT DISTINCT COUNT( po.post_Cat_ID ) AS Occurances, ca.cat_Title
FROM Posts po, Categories ca
WHERE ca.cat_ID = LEFT( po.post_Cat_ID, 2 )
GROUP BY LEFT( po.post_Cat_ID, 2 )

ps感谢@nullpointer，目前有效，我会考虑重组 对于其他读者，这里再次链接

http://mikehillyer.com/articles/managing-hierarchical-数据在 mysql/

Answer 1

向“类别”添加一列，其中给出每个类别所属的主要类别（主要类别给出自己）。所以：

cat_id | main_cat_id | title
-------+-------------+---------
01     | 01          | Science
0101   | 01          | Medicine
02     | 02          | Sport

从cat_id = main_cat_id上选择找到主要类别；在 left.cat_id = right.main_cat_id 上连接回自身以查找子类别，然后连接到 cat_id = cat_id 上的帖子。按 left.cat_id 分组并通过 cat_id 和 count(*) 进行项目。

我在 PostgreSQL 8.4 中尝试过这个，但我不明白为什么这在 MySQL 中不起作用，因为查询非常基本。我的表：

create table categories(
  cat_id varchar(40) primary key,
  main_cat_id varchar(40) not null references categories,
  title varchar(40) not null
)

create table posts (
  post_id integer primary key,
  cat_id varchar(40) not null references categories,
  title varchar(40) not null
)

我的查询（按标题而不是 ID 分组）：

select m.title, count(*)
from categories m, categories c, posts p
where m.cat_id = c.main_cat_id
  and c.cat_id = p.cat_id
group by m.title

更新：我也尝试过使用字符串操作来实现此操作，正如 OP 所尝试的那样。查询（采用 PostgreSQL 接受的符合标准的 SQL，而不是 MySQL 的方言）是：

select m.title, count(*)
from categories m, posts p
where m.cat_id = substring(p.cat_id from 1 for 2)
group by m.title;

效果很好。我无法提供有关速度的有意义的比较，但是此查询计划确实看起来比双向连接的查询计划简单一些。

Answer 2

让我建议您重新构建架构。这里你想要的是表示一个层次结构（类别），这对于关系数据库来说并不是很简单。两种常见的解决方案是邻接表和嵌套集。

邻接列表是更简单的树状结构。您将拥有一个categories 表，如下所示：

id  | name      | parent
------------------------
1   | Science   | null
2   | Sports    | null
3   | Medicine  | 1

不幸的是，该模型很难使用 SQL 来处理。相反，我们可以使用嵌套集合方法。这里每个节点都有 lft 和 rgt 值节点，它们将位于父节点的 lft 和 rgt 值之间。在您的示例中，您将拥有：

id  | name      | lft  | rgt  
-------------------------------
1   | Science   | 1    | 4    
2   | Sports    | 5    | 6 
3   | Medicine  | 2    | 3

因此，为了检索特定类别的计数，您可以简单地查询具有 lft 和 rgt 值的节点的计数介于您想要的类别之间。例如：

   SELECT COUNT(*) 
     FROM articles a
LEFT JOIN categories c ON a.category_id = c.id
    WHERE lft BETWEEN 1 AND 4 
      AND rgt BETWEEN 1 AND 4

假设您的 article 表如下所示：

id  | ... | category_id

对此进行了更详细的讨论：
http://mikehillyer.com/articles/managing-hierarchical-data-in -mysql/

---

我会提出另一个解决方案：使用标签而不是类别。您可以对给定文章使用多个标签，并简单地获取与特定标签匹配的所有文章的计数。这将更容易使用，并为您提供更大的灵活性。

为了实现这一点，您需要在文章和标签之间建立多对多关系，这通常是通过联结表实现的：

tags
id  | name

articles_tags # the junction table
article_id  | tag_id

要标记一篇文章，您只需将多个条目 INSERT 插入到 >articles_tags 表，包含正确的 article_id 和 tag_id。然后你可以像往常一样使用JOIN来获得你想要的。