问题描述

SQL 查询优化 嵌套查询

sql server sql如下：
select *from
(
select subjectCOUNT(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 subjectCOUNT(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
)

解决方案二：
SELECT SUBJECTMAX(cout)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 a
GROUP BY subject

解决方案三：

SELECT subject MAX(Subject) AS coutFROM QuestionsWHERE UserID = 'banianji'AND AskDate > '2011-01-01'AND AskDate < '2014-01-01'GROUP BY Subject