5.3.1新闻发布统计分析1.分析逻辑设计
2.数据组织设计
1）分析来源表
2）数据组织设计
表：YongRi_NewsArticles_Category
表：yongri_newsarticles_article
存储过程JZ_GetReport_XWFB
USE[Zjsme]
GO
/****** Object: StoredProcedure [dbo].[JZ_GetReport_XWFB] Script Date: 05/28/2013 17:00:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE[dbo].[JZ_GetReport_XWFB]
@dtmBeginDate DATETIME,
@dtmEndDate DATETIME
AS
SELECT CASE WHEN ISNULL(parentname,'')=''THEN'其他'ELSE parentname END,SUM(TM)
FROM(select parentname,parentname as name,sum(isnull(sl,0))tm from (
select a.categoryid,name,parentid,
parentname=
case when parentid= 0 then name
when parentid<> 0 then (select name from
YongRi_NewsArticles_Category b
where parentid= 0 and a.parentid=b.categoryid)
end,
d.sl
from YongRi_NewsArticles_Category a
left join(
select categoryid,isnull(count(1),0)sl
from yongri_newsarticles_article WHERE
UpdatedDate BETWEEN@dtmBeginDate AND@dtmEndDate group by categoryid) d
on a.categoryid=d.categoryid
)c
group by parentname
union all
select parentname,name,sl from (
select a.categoryid,name,parentid,
parentname=
case when parentid= 0 then name
when parentid<> 0 then (select name from
YongRi_NewsArticles_Category b
where parentid= 0 and a.parentid=b.categoryid)
end,
d.sl
from YongRi_NewsArticles_Category a
left join(
select categoryid,count(1)sl
from yongri_newsarticles_article group by categoryid)d
on a.categoryid=d.categoryid
)c
where parentid<> 0 )cc
GROUP BY parentname ORDER BY parentname DESC。