SQL Server查询语法，用于统计变量的最大出现次数并输出相同的结果

Question

我正在使用SQL Server 2014，我有一个简单的查询，如果我对相关表运行它，它会给我以下输出。这里是查询:

use MyDatabase
select ReservationStayID
       , NonRoomBundleID
       ,StayDate
from ResStayNonRoomBundle
where ReservationStayID = 11676

输出如下:

ReservationStayID   NonRoomBundleID     StayDate
 11676                   2              2014-07-23 00:00:00.000
 11676                   2              2014-07-24 00:00:00.000
 11676                   2              2014-07-25 00:00:00.000
 11676                   2              2014-07-26 00:00:00.000
 11676                   2              2014-07-27 00:00:00.000
 11676                   2              2014-07-28 00:00:00.000
 11676                   4              2014-07-29 00:00:00.000
 11676                   4              2014-07-30 00:00:00.000
 11676                   4              2014-07-31 00:00:00.000
 11676                   4              2014-08-01 00:00:00.000
 11676                   4              2014-08-02 00:00:00.000
 11676                   4              2014-08-03 00:00:00.000
 11676                   4              2014-08-04 00:00:00.000
 11676                   1              2014-08-05 00:00:00.000

现在，我需要修改查询以获得以下输出:

ReservationStayID   NonRoomBundle ID    MTH
       11676            2               July 2014
       11676            4               August 2014

我是这样做的:

USE MyDatabase
select ReservationStayID,max(NonRoomBundleID) AS [NonRoomBundle  ID],datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar) as  [MTH] 
from ResStayNonRoomBundle
where ReservationStayID = 11676
group by datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar), ReservationStayID

，它给了我下面的输出:

ReservationStayID   NonRoomBundle ID    MTH
       11676            4               July 2014
       11676            4               August 2014

我需要将max(NonRoomBundleID)更改为输出'NonRoomBundleID'的最大出现次数的逻辑，而不仅仅是最大值。

换句话说，我希望查询计算每个月NonRoomBundleID的最大出现次数，并输出该次数。在2014年7月的案例中，出现次数最多的NonRoomBundleID是2。因此，我希望查询输出2作为2014年7月的结果。

可能正在使用COUNT函数?如果是，我如何实现它到我现有的查询?

Answer 1

试试这个:

SELECT  ReservationStayID, NonRoombundleID, Tot, 
   CONVERT(VARCHAR, DATEPART(yyyy, DATEADD(month,  M,'1970-1-1')))+'-'+CONVERT(VARCHAR,DATEPART(month, DATEADD(month,  M,'1970-1-1'))) AS [Month] 
FROM 
   (
    SELECT ReservationStayID, NonRoombundleID, COUNT(*) AS Tot,   
     DATEDIFF(month, '1970-1-1', staydate) m, 
     ROW_NUMBER() OVER (
             partition by ReservationStayID, 
             DATEDIFF(month, '1970-1-1', staydate)  
             ORDER BY Count(*) DESC
             )  AS rownum
     FROM ResStayNonRoomBundle
     GROUP BY ReservationStayID, NonRoombundleID,
             DATEDIFF(month, '1970-1-1', staydate) 
    )  
    e 
 WHERE rownum =1

使用自己的月份格式:

SELECT  ReservationStayID, NonRoombundleID, Tot, 
   datename(m,DATEADD(MONTH, M, '1970-1-1')) + ' ' + cast(datepart(yyyy,DATEADD(MONTH, M,'1970-1-1')) as varchar)
FROM 
   (
    SELECT ReservationStayID, NonRoombundleID, COUNT(*) AS Tot,   
     DATEDIFF(month, '1970-1-1', staydate) m, 
     ROW_NUMBER() OVER (partition by ReservationStayID, DATEDIFF(month, '1970-1-1', staydate)  order by Count(*) DESC)  As rownum
     FROM ResStayNonRoomBundle
     GROUP BY ReservationStayID, NonRoombundleID, DATEDIFF(month, '1970-1-1', staydate) 
    ) 
    e 
 WHERE rownum =1

如果你想列出每个月的所有热门榜单，试试这个:

SELECT  ReservationStayID, NonRoombundleID, Tot, 
   datename(m,DATEADD(MONTH, M, '1970-1-1')) + ' ' + cast(datepart(yyyy,DATEADD(MONTH, M,'1970-1-1')) as varchar) [Month]
FROM 
   (
    SELECT ReservationStayID, NonRoombundleID, COUNT(*) AS Tot,   
     DATEDIFF(month, '1970-1-1', staydate) m
     FROM ResStayNonRoomBundle L
     GROUP BY ReservationStayID, NonRoombundleID, DATEDIFF(month, '1970-1-1', staydate) 
     HAVING COUNT(*) = (
        SELECT MAX(Tot) FROM  (
           SELECT COUNT(*) AS Tot 
           FROM ResStayNonRoomBundle T
           WHERE DATEDIFF(month, '1970-1-1', T.staydate)  = DATEDIFF(month, '1970-1-1', L.staydate) 
           GROUP BY ReservationStayID, NonRoombundleID
           ) c
        )
     )
    e 
ORDER BY m