SQL左连接——查询每个用户各操作类型的文章数量

现有基本表tb_News如下，要实现查询每个用户各种操作类型的文章数量，可以借助表连接left join实现。而且在查询结果中如果字段为NULL，将其设置为0。

实现语句：

select List.UserID,ISNULL(bianyi,0) as '编译',ISNULL(zhuanzai,0) as '转载',ISNULL(bianji,0) as '编辑' 
from (select UserID from dbo.tb_News group by UserID) List
left join (select UserID,count(NewsType)as bianyi from tb_News n where n.UserID=UserID and NewsType='编译' group by UserID) T1 on List.UserID=T1.UserID
left join (select UserID,count(NewsType)as zhuanzai from tb_News n where n.UserID=UserID and NewsType='转载' group by UserID) T2 on List.UserID=T2.UserID
left join (select UserID,count(NewsType)as bianji from tb_News n where n.UserID=UserID and NewsType='编辑' group by UserID) T3 on List.UserID=T3.UserID

查询结果：