想写一个SQL, 先分组(2个字段组合)再排序取出前N条记录, 用tnd的top n ... group by ... order by ...死活完不成这个任务, 郁闷了半天!
最终有2个方法来解决:
1. 一精通SQL Server的朋友帮忙弄了一个function,利用游标装载2个字段中的1个字段能够出现的所有值(distinct),然后来top、group,所有select出的数据union到一个临时记录集,最后返回这个记录集。 下边是那哥们的script:
代码
select
IndexDate,Domain,SearchType,TotalCount
into
#tempresult
from
dbo.StatResultByDomain
where
IndexDate
BETWEEN
'
2010-07-20
'
AND
'
2010-07-23
'
declare @indexdate datetime
declare @searchtype int
DECLARE CustomerCursor CURSOR FOR
select distinct IndexDate,SearchType from dbo.StatResultByDomain
open CustomerCursor
fetch next from CustomerCursor INTO @indexdate , @searchtype
while ( @@FETCH_STATUS = 0 )
begin
insert into #tempresult (IndexDate,Domain,SearchType,TotalCount)
select top 3 IndexDate,Domain,SearchType,TotalCount from dbo.StatResultByDomain where
IndexDate = @indexdate and SearchType = @searchtype
order by TotalCount desc
fetch next from CustomerCursor INTO @indexdate , @searchtype
end
DEALLOCATE CustomerCursor
select * from #tempresult
order by indexdate, searchtype
declare @indexdate datetime
declare @searchtype int
DECLARE CustomerCursor CURSOR FOR
select distinct IndexDate,SearchType from dbo.StatResultByDomain
open CustomerCursor
fetch next from CustomerCursor INTO @indexdate , @searchtype
while ( @@FETCH_STATUS = 0 )
begin
insert into #tempresult (IndexDate,Domain,SearchType,TotalCount)
select top 3 IndexDate,Domain,SearchType,TotalCount from dbo.StatResultByDomain where
IndexDate = @indexdate and SearchType = @searchtype
order by TotalCount desc
fetch next from CustomerCursor INTO @indexdate , @searchtype
end
DEALLOCATE CustomerCursor
select * from #tempresult
order by indexdate, searchtype
此法可行,但哥嫌麻烦,有点想舍弃。
2. 哥最终不得不google + baidu了一把, 发现了一个方法:row_number (关于它,哥有个附件附有详细资料,也可直接到http://www.wewill.cn/n32645c50.aspx这里查阅)。 主体script如下:
代码
select
IndexDate,Domain,SearchType,TotalCount
from
(
select * , row = row_number() over (partition by IndexDate,SearchType order by totalcount desc )
from [ StatResultByDomain ]
)t
where row < 4 and IndexDate BETWEEN ' 2010-07-20 ' AND ' 2010-07-23 '
order by IndexDate, SearchType
select * , row = row_number() over (partition by IndexDate,SearchType order by totalcount desc )
from [ StatResultByDomain ]
)t
where row < 4 and IndexDate BETWEEN ' 2010-07-20 ' AND ' 2010-07-23 '
order by IndexDate, SearchType
这个方法真的不错,我靠,效率应该比方法1更高一些,也简洁。
该文章100%原创,仅供个人记录和参考使用。