按字段值统计表中的数据【查询关键字在各个SEO 平台下的排名】

--业务说明：查询关键字在各个SEO 平台下的排名，并统计单个词在某段时间内在第一页、第二页、第三页、第四页的展示的总天数
--sql 说明：按字段值统计表中的数据

select 
distinct 
e.KeywordId,
e.Word,
e.[Rank],
e.SEOType,
e.CompanyName,
(select count(*) from Bus_KeywordTime d
where d.pageindex='第1页'
and d.keywordId=e.keywordId
and d.seoType=e.seoType) FirstPage,
(select count(*) from Bus_KeywordTime d
where d.pageindex='第2页'
and d.keywordId=e.keywordId
and d.seoType=e.seoType) SecondPage,
(select count(*) from Bus_KeywordTime d
where d.pageindex='第3页'
and d.keywordId=e.keywordId
and d.seoType=e.seoType) ThirdPage,
(select count(*) from Bus_KeywordTime d
where d.pageindex='第4页'
and d.keywordId=e.keywordId
and d.seoType=e.seoType) FourthPage
from 
(
select
a.KeywordId,b.Word,a.[Rank],a.PageIndex,a.SEOType,
a.DataTime,c.CompanyName,c.DomainUrl 
from dbo.Bus_KeywordTime a
left join dbo.Bus_Keyword b on a.KeywordId=b.id
left join dbo.Bus_Customer c on b.CustomerId=c.id
where 1=1
--and c.companyName = '东明'
--and a.seoType = 'yahoo'
--and b.word='pu promotional gifts'
--and a.dataTime >='2014-03-04 00:00:00:000'
--and a.dataTime <='2014-03-05 23:59:59:000'
) e