求sql 或者存储过程!<统计每个用户发表的不同种类文章数量>
--用户表
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)
)
go
insert into t_user values('张三') --1
insert into t_user values('李四') --2
insert into t_user values('王五') --3
insert 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
*/
[解决办法]
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
/*
用户姓名新闻类别体育类别娱乐类别
李四022
王五000
张三321
*/
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--排序
/*
用户姓名新闻类别体育类别娱乐类别
张三321
李四022
王五000
*/
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
/*结果：
用户姓名新闻体育娱乐
李四NULL22
王五NULLNULLNULL
张三321
*/
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 @sql
exec('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')
/*
用户名体育新闻娱乐
李四202
王五000
张三231
*/
