跳过SQL Server中的几个行计数(*)

Question

我正在研究存档类型功能，在此查询的查询数量中，每年显示的帖子数量。但是我的客户要求是，他不希望在此档案计数中最新50个帖子。我尝试了我的身边，但它不起作用。

表设计

Create Table tblBlogDetails
(
    UniqueKey UniqueIdentifier,
    BlogText NVARCHAR(MAX),
    BlogType Varchar(40),
    BlogingDateTime Datetime
)

这是工作代码

 SELECT
        DATEPART(YEAR, BlogingDateTime) AS [BlogingYear],
        DATEPART(MONTH, BlogingDateTime) AS [BlogingMonth],
        CASE WHEN DATEPART(MONTH, BlogingDateTime) =1 THEN 'January'
             WHEN DATEPART(MONTH, BlogingDateTime) =2 THEN 'February'
             WHEN DATEPART(MONTH, BlogingDateTime) =3 THEN 'March'
             WHEN DATEPART(MONTH, BlogingDateTime) =4 THEN 'April'
             WHEN DATEPART(MONTH, BlogingDateTime) =5 THEN 'May'
             WHEN DATEPART(MONTH, BlogingDateTime) =6 THEN 'June'
             WHEN DATEPART(MONTH, BlogingDateTime) =7 THEN 'July'
             WHEN DATEPART(MONTH, BlogingDateTime) =8 THEN 'August'
             WHEN DATEPART(MONTH, BlogingDateTime) =9 THEN 'September'
             WHEN DATEPART(MONTH, BlogingDateTime) =10 THEN 'October'
             WHEN DATEPART(MONTH, BlogingDateTime) =11 THEN 'November'
             WHEN DATEPART(MONTH, BlogingDateTime) =12 THEN 'December' END AS BLogingMonthName,
        COUNT(*) AS NumberOfBlog 
    FROM tblBlogdetails
    WHERE UniqueKey=@UniqueKey AND
    BlogType='BLOG' 
    GROUP BY DATEPART(YEAR, BlogingDateTime) ,DATEPART(MONTH, BlogingDateTime) 
    ORDER BY DATEPART(YEAR, BlogingDateTime) DESC ,DATEPART(MONTH, BlogingDateTime);
/*
    Current OutPut
    BlogingYear BlogingMonth BLogingMonthName  NumberOfBlog
    2017        1            January           20
    2017        3            March             25
    2017        4            April             40
    Required OutPut
    2017        1            January           20
    2017        3            March             15
*/

Answer 1

我添加了另一个过滤器。看看这是否有效。

 SELECT
    DATEPART(YEAR, BlogingDateTime) AS [BlogingYear],
    DATEPART(MONTH, BlogingDateTime) AS [BlogingMonth],
    CASE WHEN DATEPART(MONTH, BlogingDateTime) =1 THEN 'January'
         WHEN DATEPART(MONTH, BlogingDateTime) =2 THEN 'February'
         WHEN DATEPART(MONTH, BlogingDateTime) =3 THEN 'March'
         WHEN DATEPART(MONTH, BlogingDateTime) =4 THEN 'April'
         WHEN DATEPART(MONTH, BlogingDateTime) =5 THEN 'May'
         WHEN DATEPART(MONTH, BlogingDateTime) =6 THEN 'June'
         WHEN DATEPART(MONTH, BlogingDateTime) =7 THEN 'July'
         WHEN DATEPART(MONTH, BlogingDateTime) =8 THEN 'August'
         WHEN DATEPART(MONTH, BlogingDateTime) =9 THEN 'September'
         WHEN DATEPART(MONTH, BlogingDateTime) =10 THEN 'October'
         WHEN DATEPART(MONTH, BlogingDateTime) =11 THEN 'November'
         WHEN DATEPART(MONTH, BlogingDateTime) =12 THEN 'December' END AS BLogingMonthName,
    COUNT(*) AS NumberOfBlog 
FROM tblBlogdetails
WHERE UniqueKey=@UniqueKey 
       AND
      BlogType='BLOG'
      AND
      UniqueKey NOT IN (SELECT TOP 50 UniqueKey
                        FROM tblBlogdetails
                        ORDER BY BlogingDateTime DESC)
GROUP BY DATEPART(YEAR, BlogingDateTime) ,DATEPART(MONTH, BlogingDateTime) 
ORDER BY DATEPART(YEAR, BlogingDateTime) DESC ,DATEPART(MONTH, BlogingDateTime);