求sql 或者存储过程!<统计每个用户发表的不同种类文章数量>

Question

--用户表
create table t_user
( 
  userId int identity(1,1) primary key,
  userName nvarchar(10)
)
go--文章类型表
create table t_type
(
  typeId int identity(1,1) primary key,
  typeName nvarchar(10)
)
go--文章表
create table t_article
(
  articleId int identity(1,1) primary key,
  userId int not null,
  typeId int not null,
  articleName nvarchar(20)
  constraint [fk_article_user] foreign key (userId)  references t_user(userId),
  constraint [fk_article_type] foreign key (typeId)  references t_type(typeId)
)
goinsert into t_user values('张三') --1
insert into t_user values('李四') --2
insert into t_user values('王五') --3insert into t_type values('新闻') --1
insert into t_type values('体育') --2
insert into t_type values('娱乐') --3--张三（新闻3篇 ，体育2篇，娱乐1篇）
insert into t_article values(1,1,'新闻标题1')
insert into t_article values(1,1,'新闻标题2')
insert into t_article values(1,1,'新闻标题3')
insert into t_article values(1,2,'体育标题1')
insert into t_article values(1,2,'体育标题2')
insert into t_article values(1,3,'娱乐标题1')--李四（新闻0篇，体育2篇，娱乐2篇）
insert into t_article values(2,2,'体育标题1')
insert into t_article values(2,2,'体育标题2')
insert into t_article values(2,3,'娱乐标题1')
insert into t_article values(2,3,'娱乐标题2')--王五（无发表）--问题：需要统计每个用户发表的不同种类文章数量,格式如下：
/*
 用户姓名 新闻类别 体育类别 娱乐类别
  张三       3        2        1
  李四       0        2        2
  王五       0        0        0
*/

Answer 1

SELECT 
用户姓名=username,
新闻类别 = ISNULL([新闻], 0),
体育类别 = ISNULL([体育], 0),
娱乐类别 = ISNULL([娱乐], 0)
FROM
(
SELECT a.username,c.typename, cnt=COUNT(b.articleid)
FROM t_user A
LEFT JOIN t_article b
ON A.userId=b.userId
LEFT JOIN t_type C
ON B.typeid = c.typeid
GROUP BY a.userid,a.username,c.typeid,c.typename
) a
PIVOT
(
MAX(cnt) FOR typename IN([新闻],[体育],[娱乐])
) b
/*
用户姓名 新闻类别 体育类别 娱乐类别
李四 0 2 2
王五 0 0 0
张三 3 2 1
*/

Answer 2

SELECT 
用户姓名=username,
新闻类别 = ISNULL([新闻], 0),
体育类别 = ISNULL([体育], 0),
娱乐类别 = ISNULL([娱乐], 0)
FROM
(
SELECT a.userid,a.username,c.typename, cnt=COUNT(b.articleid)
FROM t_user A
LEFT JOIN t_article b
ON A.userId=b.userId
LEFT JOIN t_type C
ON B.typeid = c.typeid
GROUP BY a.userid,a.username,c.typeid,c.typename
--ORDER BY username
) a
PIVOT
(
MAX(cnt) FOR typename IN([新闻],[体育],[娱乐])
) b
ORDER BY userid --排序
/*
用户姓名 新闻类别 体育类别 娱乐类别
张三 3 2 1
李四 0 2 2
王五 0 0 0
*/

Answer 3

SELECT userName 用户姓名,[新闻],[体育],[娱乐] FROM 
(SELECT t3.userName,t2.typeName,ISNULL(COUNT(*),0) Qty FROM t_article t1 
LEFT JOIN t_type t2 ON t1.typeId=t2.typeId
RIGHT JOIN t_user t3 ON t1.userId=t3.userId
GROUP BY t3.userName,t2.typeName) AS userName
PIVOT(SUM(Qty) FOR typeName IN([新闻],[体育],[娱乐])) a/*结果：
用户姓名 新闻 体育 娱乐
李四 NULL 2 2
王五 NULL NULL NULL
张三 3 2 1
*/

Answer 4

动态sql语法declare @sql varchar(8000)
set @sql=''
select @sql=@sql + ',['+rtrim(typeName)+']=count(case rtrim(typeName) when '''+rtrim(typeName)+''' then 1 end)'
from t_type group by rtrim(typeName)
print @sqlexec('select userName'+@sql+' from t_user a
left join t_article b on b.userId=a.userId
left join t_type c on b.typeId=c.typeId 
group by userName')/*
用户名 体育 新闻 娱乐
李四 2 0 2
王五 0 0 0
张三 2 3 1
*/