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

 

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

转载于:https://www.cnblogs.com/MaoBisheng/archive/2010/07/14/1776995.html