MySQL 列出带有链接的类别

Question

抱歉，我会说一点英语。没关系:

SELECT * FROM `category` WHERE `state` = TRUE ORDER BY `title` ASC

我想要:

SELECT *, cnt FROM `category` WHERE `state` = TRUE ORDER BY `title` ASC

cnt为count link(category-id)/category(id)。

CREATE TABLE category ( id );
CREATE TABLE link ( id, category-id ); -- link(category-id)=category(id)

每个类别有多少个链接？

谢谢。

Answer 1

加入link表并按要从category表中选择的所有列进行分组

SELECT c.id, c.title, count(l.id) as cnt 
FROM `category` c
left join link l on l.category_id = c.id
WHERE c.`state` = 1
GROUP BY c.id, c.title
ORDER BY c.`title` ASC