如何在mysql中显示包含项目的类别列表 [英] How to display the list of categories which contain items in mysql

问题描述

我想问一下如何显示包含项目的类别列表.不应显示没有从其他表链接的项目的类别.此外，应根据项目数量显示类别.

I would like to ask how to display a list of categories which contain items. Categories without item linked from other table should not be displayed. Also,categories should be displayed according to the number of items.

请考虑两个表:

   |------- categorytbl----|
   cat_id  |    cat_name
    1      |    Pet
    2      |    Person
    3      |    Places
   -------------------------

   |------ itemstbl -----|
    item_id      item_description     cat_id
     1                John              2
     2                Peter             2
     3                 Cat              1

如果我要查询上表，输出应该是:

If I am going to query the above table, the output should be:

宠物(1)、人物(2)

Pet(1) , Person(2)

而且，我不应该包含 Places 类别，因为它不包含任何项目.

And, I should not include Places category since it does not contain any item.

我尝试了我的查询:

   SELECT * FROM categorytbl a, itemstbl b WHERE a.cat_id=b.cat_id

推荐答案

您需要从两个表中选择所有元素并进行内连接，这样您只能得到两边匹配的结果

You need to select all elements from both tables and inner join them so you get only matching results on both sides

SELECT cat_name, COUNT(item_id) AS items_count 
FROM categorytbl AS cat
INNER JOIN itemstbl AS item ON cat.cat_id = item.cat_id
GROUP BY cat.cat_id
ORDER BY items_count DESC

为了更好地理解 JOIN 在 SQL 中的工作原理，请看这张图:

To get better understanding of how JOINs work in SQL have a look at this picture:

但正如其他人所说，请阅读您正在使用的 RDBMS 的相关文档或 SQL 教程，以帮助您了解它.

But as other people have said read the relevent documentation for the RDBMS you're using or a tutorial for SQL that would help you get your head arround it.

这篇关于如何在mysql中显示包含项目的类别列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！