数据库表结构分析
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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:
-- Create date:
-- 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