获取每月中 name字段 出现次数最多的数据

/*  思路： 1.先计算出   每月 每个name 出现次数 num

                2.给每个月 每个name 数据 ，按照出现次数num 从大到小   排序, 添加   行号

                3. 按 每月分组，取 行号最小的 数据

 

*/

 

select *
from (  select name,date1,ROW_NUMBER()over(order by num desc) as clow
           from (  select name,FORMAT(cast(date1 as date) ,'yyyyMM') as date1 ,count(*) as num
                      from cdr.dbo.test_lkj
                      group by FORMAT(cast(date1 as date),'yyyyMM'),name
  
                     ) a

         ) c
where clow in (
  select min(clow)
  from (
  select name,date1,ROW_NUMBER()over(order by num desc) as clow
  from (  select name,FORMAT(cast(date1 as date) ,'yyyyMM') as date1 ,count(*) as num
  --FORMAT(cast(date as date),name
  from cdr.dbo.test_lkj
  group by FORMAT(cast(date1 as date),'yyyyMM'),name
  
) a) b
group by b.date1)