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小白,求大家给优化下,谢谢...

sql
suannai0314
鹳狸媛 楼主的这个问题解决了么?下边的答案能解决问题么?如果可以麻烦点击答案旁的√采纳哦~如果没有也可以将自己的答案贴上然后进行采纳的。
大约 6 年之前 回复

3个回答

;
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 SUBJECT,MAX(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 cout
FROM Questions
WHERE UserID = 'banianji'
AND AskDate > '2011-01-01'
AND AskDate < '2014-01-01'
GROUP BY Subject
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问