SQL 查询优化 嵌套查询

Question

sql server ,sql如下：
select *from
(
select subject,COUNT(Subject) as cout from Questions where UserID ='banianji' and AskDate>'2011-01-01' and AskDate<'2014-01-01'
group by Subject
) as a
where a.cout=
(
select MAX(cout)from

(
select subject,COUNT(Subject) as cout from Questions where UserID ='banianji' and AskDate>'2011-01-01' and AskDate<'2014-01-01'
group by Subject
) as b
)
--------------读了上面的SQL，大家应该能明白我想得到什么数据：我要获取值最大的所有的记录（最大值相同的记录可能不止一条，我要把所有的最大值的记录都获取到）。上面的SQL的确能得到我想要的结果，但是，总感觉不好啊，本人sql小白，求大家给优化下，谢谢...

Answer 1

;
WITH cte
AS ( SELECT subject ,
COUNT(Subject) AS cout
FROM Questions
WHERE UserID = 'banianji'
AND AskDate > '2011-01-01'
AND AskDate < '2014-01-01'
GROUP BY Subject
)
SELECT *
FROM cte AS a
WHERE a.cout = ( SELECT MAX(cout)
FROM cte AS b
)