两个表:articles(文章表)、articles_sort(文章类别表)
articles: Id, Title, SortId.
articles_sort: Id, SortName.
其中:articles.[SortId]是articles_sort表的外键。 现想实现各个类别的文章统计,示意如下:
类别一:100篇
类别二:200篇
......
以下代码可实现文章数不为空的类别统计:
select
a.Sort,count(*) as Num
from
Article_Sort a , Article b
where
a.Id = b.SortId
group by
a.Sort
order by Num desc但我想要罗列全部的类别,即使该类别的文章数为0,也要把0列上。拜谢各位大侠!
articles: Id, Title, SortId.
articles_sort: Id, SortName.
其中:articles.[SortId]是articles_sort表的外键。 现想实现各个类别的文章统计,示意如下:
类别一:100篇
类别二:200篇
......
以下代码可实现文章数不为空的类别统计:
select
a.Sort,count(*) as Num
from
Article_Sort a , Article b
where
a.Id = b.SortId
group by
a.Sort
order by Num desc但我想要罗列全部的类别,即使该类别的文章数为0,也要把0列上。拜谢各位大侠!
a.Sort,count(*) as Num
from
Article_Sort a left join Article b
on
a.Id = b.SortId
group by
a.Sort
order by Num desc
改为左连接即可
left join (select SortId , count(1) cnt from articles group by SortId) n
on m.id = n.SortId
isnull(a.Sortname,0) as Sortname,count(*) as Num
from
Article_Sort a , Article b
where
a.Id = b.SortId
group by
a.Sort
order by Num desc
select SortName, num=count(*) from articles_sort b
left join articles a
on b.Id = a.SortId
group by b.Id