计算属于 MySQL 中某个类别的记录 [英] Counting records belonging to a category in MySQL
问题描述
我一直在与一些 SQL 作斗争，但似乎无法理解它.
I've been battling with some SQL and can't seem to get my head around it.
我有两个表格，一个是类别列表，另一个是我所有的文章.
I have two tables, one with the list of categories and another with all my articles.
我想要做的是找出每个类别有多少篇文章.
What i'm trying to do is find how many articles are present for each category.
这是我目前使用的 SQL
Here is the SQL I have so far
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 的原因是只获取主要类别，因为我列出了以下类别......例如
The reason I use LEFT is to only get the main categories as I have listed categories as the following... for example
Science = 01
Medicine = 0101
Sport = 02
因此，asprin 上的帖子的 cat_ID 为 0101.(然后将 0101、0102、0103 等修剪为 01).基本上我对子类别不感兴趣.
Posts on say asprin would therefore have a cat_ID as 0101. (LEFT would then trim 0101, 0102, 0103 etc to just 01). Basically im not interested in the subcategories.
提前致谢
结果
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 )
附言谢谢@nullpointer，它暂时有效，我会考虑重组对于其他读者，这里再次链接
p.s. thanks @nullpointer, it works for the moment, i'll look into restructuring for other readers heres the link again
http://mikehillyer.com/articles/managing-hierarchical-mysql中的数据/
推荐答案
让我建议您改为重构架构.您在这里想要的是表示层次结构(类别)，这对于关系数据库来说并不是很简单.两种常见的解决方案是邻接表和嵌套集.
Let me suggest you to restructure the schema instead. What you want here is to represent a hierarchical structure (categories), which is not really straightforward to do with relational databases. Two common solutions are the adjacency list and the nested set.
邻接表是更直接的树状结构.您将有一个 categories 表，如:
The adjacency list is more a straightforward tree-like structure. You'll have a categories table like:
id | name | parent
------------------------
1 | Science | null
2 | Sports | null
3 | Medicine | 1
不幸的是，这个模型很难使用 SQL 来处理.相反，我们可以使用嵌套集方法.这里每个节点都有 lft 和 rgt 值节点，它们将在父节点的 lft 和 rgt 值之间.在您的示例中，您将拥有:
Unfortunately this model is hard to work with using SQL. Instead, we can the nested set approach. Here every node has lft and rgt values node which will be between the parent's lft and rgt values. In your example you'll have:
id | name | lft | rgt
-------------------------------
1 | Science | 1 | 4
2 | Sports | 5 | 6
3 | Medicine | 2 | 3
因此，为了检索某个类别的计数，您可以简单地查询在您想要的类别之间具有 lft 和 rgt 值的节点的计数.例如:
So in order to retrieve a count for a certain category, you can simply query the count of nodes that have a lft and rgt value in between the category you want. For example:
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 表如下所示:
Assuming your article table looks like:
id | ... | category_id
更详细的讨论在:
http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
我将提出另一种解决方案:使用标签而不是类别.您可以为给定的文章使用多个标签，只需获取与某个标签匹配的所有文章的数量.这将更容易使用，并为您提供更大的灵活性.
I'll propose another solution: use tags rather than categories. You can use multiple tags for a given article and simply get the count of all articles matching a certain tag. This will be a lot easier to work with and also give you a lot more flexibility.
要实现这一点，您需要在文章和标签之间建立多对多关系，这通常是通过联结表实现的:
To accomplish this, you'll need a many-to-many relationship between articles and tags, which is usually implemented with a junction table:
tags
id | name
articles_tags # the junction table
article_id | tag_id
要标记一篇文章，您只需使用正确的article_id 和tag_id 将多个条目INSERT 插入到articles_tags 表中>.然后你可以像往常一样使用 JOIN 来得到你想要的.
To tag an article, you simply INSERT multiple entries into the articles_tags table with the correct article_id and tag_id. Then you can use JOINs as usual to get what you want.
这篇关于计算属于 MySQL 中某个类别的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
