--取出每年每月中最多的input_num
select input_date_year,input_date_month,input_num,max(cot)
from (
select year(input_date) input_date_year,month(input_date) input_date_month,input_num,count(1) cot
from T_Temp
group by year(input_date),month(input_date),input_num
) a
group by input_date_year,input_date_month,input_num
select input_date_year,input_date_month,input_num,max(cot)
from (
select year(input_date) input_date_year,month(input_date) input_date_month,input_num,count(1) cot
from T_Temp
group by year(input_date),month(input_date),input_num
) a
group by input_date_year,input_date_month,input_num
select distinct input_date, max(cot)
from
(
select input_date,count(1) as cot from T_Temp group by input_date
) a
select m.* from
(
select convert(varchar(10),input_date,120) [month] , input_num , count(*) cnt from t_temp group by convert(varchar(10),input_date,120) , input_num
) m where cnt = (select max(cnt) from
(
select convert(varchar(10),input_date,120) [month] , input_num , count(*) cnt from t_temp group by convert(varchar(10),input_date,120) , input_num
) n
where n.[month] = m.[month] and n.input_num = m.input_num)