sql 優化 嵌套-SQL 查詢優化 嵌套查詢

SQL 查詢優化 嵌套查詢

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小白，求大家給優化下，謝謝...

最佳回答：

;
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
)