sql-server - 查询从子查询中查找前 3 个

Question

我创建了一个查询

SELECT  Years 
        ,Months
        ,VulName
        ,Vulcount
        ,SUM(VulCount) OVER(PARTITION BY  VulId) AS  Totals 
FROM
( 
    SELECT  YEAR(outertblissues.OpenDt) AS Years 
            ,MONTH(outertblissues.OpenDt) AS Months
            ,outertblvulnerability.VulId
            ,outertblvulnerability.VulName
            ,COUNT(outertblvulnerability.VulId) Vulcount
    FROM    tbl_apptestdetails AS outertblapptestdetails 
            INNER JOIN tbl_applicationlist AS outertblapplicationlist 
                ON outertblapptestdetails.appid=outertblapplicationlist.appid 
            INNER JOIN tbl_bu AS outertblbu 
                ON outertblbu.buid=outertblapplicationlist.buid 
            INNER JOIN tbl_Issues AS outertblissues 
                ON outertblapptestdetails.testdetailid=outertblissues.testdetailid 
                   AND outertblissues.Status NOT IN('1','4')  
            INNER JOIN tbl_vulnerability AS outertblvulnerability 
                ON outertblissues.VulId=outertblvulnerability.VulId
    GROUP   BY YEAR(outertblissues.OpenDt)
               ,MONTH(outertblissues.OpenDt)
               ,outertblvulnerability.VulId
               ,outertblvulnerability.VulName
) a
ORDER BY Totals DESC

给出输出

我只想选择最大的 top(3) Totals 中的所有记录，要记住的一件事是，如果多个 VulName 的 Totals 相同，那么结果应该只包含不同的前 3 个 VulName ，Totals是我的最后一列

Answer 1

这可能对您有用，将您的第一个查询包装在一个 cte 中(删除 order by 子句)，然后使用另一个 cte 建立您感兴趣的前三行，然后通过内部连接过滤您的最终选择；

WITH cte_Select AS
(
    SELECT  Years 
            ,Months
            ,VulName
            ,Vulcount
            ,SUM(VulCount) OVER(PARTITION BY  VulId) AS  Totals 
    FROM
    ( 
        SELECT  YEAR(outertblissues.OpenDt) AS Years 
                ,MONTH(outertblissues.OpenDt) AS Months
                ,outertblvulnerability.VulId
                ,outertblvulnerability.VulName
                ,COUNT(outertblvulnerability.VulId) Vulcount
        FROM    tbl_apptestdetails AS outertblapptestdetails 
                INNER JOIN tbl_applicationlist AS outertblapplicationlist 
                    ON outertblapptestdetails.appid=outertblapplicationlist.appid 
                INNER JOIN tbl_bu AS outertblbu 
                    ON outertblbu.buid=outertblapplicationlist.buid 
                INNER JOIN tbl_Issues AS outertblissues 
                    ON outertblapptestdetails.testdetailid=outertblissues.testdetailid 
                       AND outertblissues.Status NOT IN('1','4')  
                INNER JOIN tbl_vulnerability AS outertblvulnerability 
                    ON outertblissues.VulId=outertblvulnerability.VulId
        GROUP   BY YEAR(outertblissues.OpenDt)
                   ,MONTH(outertblissues.OpenDt)
                   ,outertblvulnerability.VulId
                   ,outertblvulnerability.VulName
    ) a            
)

, cte_Top3_Vulcount as
(

    SELECT TOP (3)
        Totals
    FROM cte_Select
    GROUP BY Totals
    ORDER BY Totals DESC
)

SELECT  s.Years 
        ,s.Months
        ,s.VulName
        ,s.Vulcount
        ,s.Totals 
FROM cte_Select s
INNER JOIN cte_Top3_Vulcount t3
    on t3.[Totals] = s.[Totals]

其实一个小时前需求已经明确了；

Top three unique Vulname ordered by Totals

在此基础上，将cte_Top3_Vulcount内容替换为以下内容；

    SELECT TOP (3)
        VulName
    FROM cte_Select
    GROUP BY VulName
    ORDER BY max(Totals) DESC

整个查询将变为；

WITH cte_Select AS
(
    SELECT  Years 
            ,Months
            ,VulName
            ,Vulcount
            ,SUM(VulCount) OVER(PARTITION BY  VulId) AS  Totals 
    FROM
    ( 
        SELECT  YEAR(outertblissues.OpenDt) AS Years 
                ,MONTH(outertblissues.OpenDt) AS Months
                ,outertblvulnerability.VulId
                ,outertblvulnerability.VulName
                ,COUNT(outertblvulnerability.VulId) Vulcount
        FROM    tbl_apptestdetails AS outertblapptestdetails 
                INNER JOIN tbl_applicationlist AS outertblapplicationlist 
                    ON outertblapptestdetails.appid=outertblapplicationlist.appid 
                INNER JOIN tbl_bu AS outertblbu 
                    ON outertblbu.buid=outertblapplicationlist.buid 
                INNER JOIN tbl_Issues AS outertblissues 
                    ON outertblapptestdetails.testdetailid=outertblissues.testdetailid 
                       AND outertblissues.Status NOT IN('1','4')  
                INNER JOIN tbl_vulnerability AS outertblvulnerability 
                    ON outertblissues.VulId=outertblvulnerability.VulId
        GROUP   BY YEAR(outertblissues.OpenDt)
                   ,MONTH(outertblissues.OpenDt)
                   ,outertblvulnerability.VulId
                   ,outertblvulnerability.VulName
    ) a            
)

, cte_Top3_VulName as
(
    SELECT TOP (3)
        VulName
    FROM cte_Select
    GROUP BY VulName
    ORDER BY max(Totals) DESC
)

SELECT  s.Years 
        ,s.Months
        ,s.VulName
        ,s.Vulcount
        ,s.Totals 
FROM cte_Select s
INNER JOIN cte_Top3_VulName t3
    on t3.VulName = s.VulName