一千萬個為什麽

搜索

進一步過濾SQL結果


我有一個使用SQL 2005返回正確結果集的查詢。它如下:

select 
    case
    when convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) = '1969 Q4' then '2009 Q2'
    else convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101)
    end as [Quarter],
    bugtypes.bugtypename,
    count(bug.bugid) as [Total]
from bug left outer join bugtypes on bug.crntbugtypeid = bugtypes.bugtypeid and bug.projectid = bugtypes.projectid
where 
    (bug.projectid = 44 
    and bug.currentowner in (-1000000031,-1000000045) 
    and bug.crntplatformid in (42,37,25,14))
or
    (bug.projectid = 44 
    and bug.currentowner in (select memberid from groupmembers where projectid = 44 and groupid in (87,88))
    and bug.crntplatformid in (42,37,25,14))

group by
    case
    when convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101) = '1969 Q4' then '2009 Q2' else convert(varchar(4),datepart(yyyy,bug.datecreated),101)+ ' Q' +convert(varchar(2),datepart(qq,bug.datecreated),101)
    end,
    bugtypes.bugtypename
order by 1,3 desc

它生成一個精美分組的年份和季度列表,一個關聯的描述符,以及按遞減計數順序的事件計數。我想做的是進一步過濾這個,所以它只顯示每季度提交的10個最常見的事件。

我正在努力的是如何采用這個結果集並實現這一目標。

最佳答案

您已按季度和總計訂購。你嘗試過使用過:

SELECT TOP 10

.....其余的查詢

EDIT: After reading your comment, I realize that you need to use RANK and Partition to make this work. You can wrap in a CTE as shown below:

;WITH IncidentsTable AS
(
   ... Insert Your Query here ...
)
SELECT * FROM
(
    SELECT [Quarter],
       BugTypeName,
       Total,
       Rank() OVER (Partition BY [Quarter] order by Total DESC) AS Ranking
     FROM
    IncidentsTable
)
WHERE
    Ranking <= 10
ORDER BY
      Quarter, Total;

轉載註明原文: 進一步過濾SQL結果

猜你喜歡