类别表:
id, name
---------
1 cat1
2 cat2
3 cat3
4 cat4
文章表:
id, cid, title
--------------
1 1 title1
2 3 title2
3 1 title3
4 2 title4
如何在MySQL中选择所有类别并计算每个类别文章的数量?我需要一个 mysql 查询,它将选择属于每个类别的所有类别和文章总数,并按类别 ID 排序。
预期输出表:
+-------+-------+
| name | count |
+-------+-------+
| cat1 | 2 |
| cat2 | 1 |
| cat3 | 1 |
| cat4 | 0 |
+-------+-------+
SELECT
c.`name`,
COUNT(a.cid) AS `count`
FROM
categories c
LEFT JOIN article a ON c.id = a.cid
GROUP BY
c.`name`
ORDER BY
c.`name`
你也可以使用子查询,比如
SELECT c.name,(select count(*) from article where cid = c.id) as count FROM cat c WHERE 1 GROUP BY c.id
试试这个:
SELECT c.`name`, count(a.cid) as count FROM category
LEFT JOIN article
ON c.id = a.cid
GROUP BY a.cid
select c.name,count(b.title)count from category a, article b where c.id = a.cid group by c.name order by a.cid;
我希望这会起作用。请检查。
select
Category.name.
ifnull(count(Article.cid),0) as count
FROM
Category
LEFT JOIN
Article
on
Article.cid=Category.id
group by
Category.Id