表结构如下,
CREATE TABLE [dbo].[Rank_KeyName](
[ID] [int] IDENTITY(1,1) NOT NULL,
[R_Year] [int] NULL,年 [R_Month] [int] NULL,月
[KeyName] [nchar](960) COLLATE Chinese_PRC_CI_AS NULL,关键词名称
[R_Total] [int] NULL, 数量
CONSTRAINT [PK_Rank_KeyName] PRIMARY KEY CLUSTERED
(
[ID] ASC
)要求按年/月数组,并且查出该月的排名前10的关键词
结果显示如下: 日期, 第一名关键词,数量,第二名关键词,数量.......
2011-1,AA关键词, 100, BB关键词 ,90,..................
CREATE TABLE [dbo].[Rank_KeyName](
[ID] [int] IDENTITY(1,1) NOT NULL,
[R_Year] [int] NULL,年 [R_Month] [int] NULL,月
[KeyName] [nchar](960) COLLATE Chinese_PRC_CI_AS NULL,关键词名称
[R_Total] [int] NULL, 数量
CONSTRAINT [PK_Rank_KeyName] PRIMARY KEY CLUSTERED
(
[ID] ASC
)要求按年/月数组,并且查出该月的排名前10的关键词
结果显示如下: 日期, 第一名关键词,数量,第二名关键词,数量.......
2011-1,AA关键词, 100, BB关键词 ,90,..................
SET @i=10WHILE @i>0
SELECT @s=N',[1'+@i+']=max(case when Row='+@i+N' then [R_Total] end)'+@s,@i=@i-1
EXEC(N'SELECT [R_Year],[R_Month]'+@s+N'
FROM (select *, row=row_number()over(partition by [R_Year],[R_Month] order by [R_Total] desc)from [Rank_KeyName]) as a
where rowM=10')
go
select R_Year,
max(case px when 1 then KeyName else '' end) [第1名关键词],
max(case px when 1 then R_Total else 0 end) [数量],
max(case px when 2 then KeyName else '' end) [第2名关键词],
max(case px when 2 then R_Total else 0 end) [数量],
...
max(case px when 10 then KeyName else '' end) [第10名关键词],
max(case px when 10 then R_Total else 0 end) [数量]
from
(
select m.* , (select count(1) from
(
select R_Year , KeyName , sum(R_Total) R_Total from Rank_KeyName group by R_Year , KeyName
) n where n.R_Year = m.R_Year and n.R_Total > m.R_Total) + 1 as px from
(
select R_Year , KeyName , sum(R_Total) R_Total from Rank_KeyName group by R_Year , KeyName
) m
) t
select R_Year,
max(case px when 1 then KeyName else '' end) [第1名关键词],
max(case px when 1 then R_Total else 0 end) [数量],
max(case px when 2 then KeyName else '' end) [第2名关键词],
max(case px when 2 then R_Total else 0 end) [数量],
...
max(case px when 10 then KeyName else '' end) [第10名关键词],
max(case px when 10 then R_Total else 0 end) [数量]
from
(
select m.* , (select count(1) from
(
select R_Year , KeyName , sum(R_Total) R_Total from Rank_KeyName group by R_Year , KeyName
) n where n.R_Year = m.R_Year and n.R_Total > m.R_Total) + 1 as px from
(
select R_Year , KeyName , sum(R_Total) R_Total from Rank_KeyName group by R_Year , KeyName
) m
) t
group by R_Year--sql 2005用row_number实现,不考虑名次重复
select R_Year,
max(case px when 1 then KeyName else '' end) [第1名关键词],
max(case px when 1 then R_Total else 0 end) [数量],
max(case px when 2 then KeyName else '' end) [第2名关键词],
max(case px when 2 then R_Total else 0 end) [数量],
...
max(case px when 10 then KeyName else '' end) [第10名关键词],
max(case px when 10 then R_Total else 0 end) [数量]
from
(
select m.* , row_number() over(partition by R_Year order by R_Total desc) as px from
(
select R_Year , KeyName , sum(R_Total) R_Total from Rank_KeyName group by R_Year , KeyName
) m
) t
group by R_Year
[R_Year],[R_Month],
[KeyName1]=max(case when Row=1 then [KeyName] end),
[1]=max(case when Row=1 then [R_Total] end),
[KeyName2]=max(case when Row=2 then [KeyName] end),
[2]=max(case when Row=2 then [R_Total] end),
[KeyName3]=max(case when Row=3 then [KeyName] end),
[3]=max(case when Row=3 then [R_Total] end),
[KeyName4]=max(case when Row=4 then [KeyName] end),
[4]=max(case when Row=4 then [R_Total] end),
[KeyName5]=max(case when Row=5 then [KeyName] end),
[5]=max(case when Row=5 then [R_Total] end),
[KeyName6]=max(case when Row=6 then [KeyName] end),
[6]=max(case when Row=6 then [R_Total] end),
[KeyName7]=max(case when Row=7 then [KeyName] end),
[7]=max(case when Row=7 then [R_Total] end),
[KeyName8]=max(case when Row=8 then [KeyName] end),
[8]=max(case when Row=8 then [R_Total] end),
[KeyName9]=max(case when Row=9 then [KeyName] end),
[9]=max(case when Row=9 then [R_Total] end),
[KeyName10]=max(case when Row=10 then [KeyName] end),
[10]=max(case when Row=10 then [R_Total] end)
FROM (select *, row=row_number()over(PARTITION by [R_Year],[R_Month] order by [R_Total] desc)from [Rank_KeyName] ) as a
where row<=10 GROUP BY [R_Year],[R_Month]
go
IF OBJECT_ID('Tempdb..#') IS NOT NULL
DROP TABLE #
select *, ID=IDENTITY(INT,1,1) INTO # FROM [Rank_KeyName] ORDER BY [R_Year],[R_Month],[R_Total]
SELECT
[R_Year],[R_Month],
[KeyName1]=max(case when Row=1 then [KeyName] end),
[1]=max(case when Row=1 then [R_Total] end),
[KeyName2]=max(case when Row=2 then [KeyName] end),
[2]=max(case when Row=2 then [R_Total] end),
[KeyName3]=max(case when Row=3 then [KeyName] end),
[3]=max(case when Row=3 then [R_Total] end),
[KeyName4]=max(case when Row=4 then [KeyName] end),
[4]=max(case when Row=4 then [R_Total] end),
[KeyName5]=max(case when Row=5 then [KeyName] end),
[5]=max(case when Row=5 then [R_Total] end),
[KeyName6]=max(case when Row=6 then [KeyName] end),
[6]=max(case when Row=6 then [R_Total] end),
[KeyName7]=max(case when Row=7 then [KeyName] end),
[7]=max(case when Row=7 then [R_Total] end),
[KeyName8]=max(case when Row=8 then [KeyName] end),
[8]=max(case when Row=8 then [R_Total] end),
[KeyName9]=max(case when Row=9 then [KeyName] end),
[9]=max(case when Row=9 then [R_Total] end),
[KeyName10]=max(case when Row=10 then [KeyName] end),
[10]=max(case when Row=10 then [R_Total] end)
FROM
(select *, row=(SELECT COUNT(1) FROM # WHERE [R_Year]=t.[R_Year] AND [R_Month]=t.[R_Month] AND ID<=t.ID) from # AS t) as a
where row<=10
GROUP BY [R_Year],[R_Month]
实在好佩服你们两个的实力!