SQL语句--分组的Top查询

分组的Top查询：

SELECT ROW_NUMBER() OVER (order by a.Host,EnterTimes DESC)as RowNumber,
--SELECT IDENTITY(int,1,1) as RowNumber,
a.Host,a.PageURL,a.EnterTimes,a.BounceTimes INTO #TempEntryUrlList
FROM
(
    SELECT Host                                                            --Host
        ,EntranceURL as PageURL                                            --页面地址
        ,count(EntranceURL) as EnterTimes                                --进入次数
        ,sum(CASE WHEN IsBounce=1 THEN 1 ELSE 0 END ) AS BounceTimes    --跳出次数
    FROM UserLoyalty WITH(nolock)
    GROUP BY Host,EntranceURL
) a

需要对以上#TempEntryUrlList临时表做查询：按照Host分组，查询每个Host的EnterTimes的Top 100数据：

Select *
FROM #TempEntryUrlList A
Where EXISTS
        (SELECT COUNT(1)
         FROM #TempEntryUrlList
         WHERE Host = A.Host AND RowNumber < A.RowNumber
         HAVING COUNT(1) < 100)
ORDER BY Host,EnterTimes DESC 

 

补充：感谢“我爱菊花”的回复，用ROW_NUMBER的PARTITION属性就可以一句话实现，免去临时表:

SELECT A.*
FROM
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Host ORDER BY Host,count(EntranceURL) DESC) AS ROW,
    Host, 
    EntranceURL as PageURL
    ,count(EntranceURL) as EnterTimes
    ,sum(CASE WHEN IsBounce=1 THEN 1 ELSE 0 END ) AS BounceTimes
    FROM UserLoyalty
    GROUP BY Host,EntranceURL
) A
WHERE A.ROW<=100

 

注：数据量大的话，感觉性能不是很好，数据量小的时候，还是可以用的。