2 xiaojia7283 xiaojia7283 于 2013.12.26 11:52 提问

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

3个回答

DBA_Huangzj
DBA_Huangzj   Ds   Rxr 2013.12.27 16:04
已采纳

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

DBA_Huangzj
DBA_Huangzj   Ds   Rxr 2013.12.26 12:34

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

DBA_Huangzj
DBA_Huangzj   Ds   Rxr 2013.12.26 17:15
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
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!