select isnull(sum(total),0) from UserResult a where qs=57 and bonusType='补贴' and userid in
(select userid from (select userid ,sum(total) vSum from UserResult where bonustype<>'基本奖金' group by userid) c where c.vSum>=3000)现在
select isnull(sum(total),0) from UserResult a where qs=57 and bonusType='补贴';
select userid from (select userid ,sum(total) vSum from UserResult where bonustype<>'基本奖金' group by userid) c where c.vSum>=3000;
单独执行都不超过1秒。而上面的SQL语句居然要执行40秒。userid,bonusType都设置了索引。
请问有什么好方法是SQL运行更快些
(select userid from (select userid ,sum(total) vSum from UserResult where bonustype<>'基本奖金' group by userid) c where c.vSum>=3000)现在
select isnull(sum(total),0) from UserResult a where qs=57 and bonusType='补贴';
select userid from (select userid ,sum(total) vSum from UserResult where bonustype<>'基本奖金' group by userid) c where c.vSum>=3000;
单独执行都不超过1秒。而上面的SQL语句居然要执行40秒。userid,bonusType都设置了索引。
请问有什么好方法是SQL运行更快些
select isnull(sum(total),0) from UserResult a where qs=57 and bonusType='补贴'' and EXISTS
(select userid from (select userid ,sum(total) vSum from UserResult where bonustype <> '基本奖金' group by userid) c where c.vSum>=3000 and userid=a.userid)
也需要30秒。。
各位有什么好方法没有
select isnull(sum(total),0) from UserResult where qs=57 and bonusType='补贴' and
userid in (select userid from UserResult where bonustype <> '基本奖金' group by userid having sum(total)>=3000)
FROM UserResult
WHERE bonustype <> '基本奖金'
GROUP BY userid
HAVING sum(total) >= 3000另外想问问lz是怎么对qs、bonusType和userid建的索引,是复合索引还是单列索引,UserResult表的聚集索引建在哪一列上了?
这个表的有多大,它的数据分布有什么特征?比如:bonustype <> '基本奖金' 的行有多少,bonusType='补贴' 的行有多少, qs=57 的行有多少?
与此情况类似,试了两天,长语句执行时狂读硬盘,查询速度慢了差不多10倍.
(SELECT sum(total) as vSum,userid
FROM UserResult
WHERE bonustype = '补贴' and qs=57
GROUP BY userid
HAVING sum(total) > = 3000) a
我不懂你们的业务,只是这么猜de:)
userid in (select userid from UserResult where bonustype <> '基本奖金' group by userid having sum(total)>=3000)
正确结贴给分!
select isnull(b.vSum,0)
from
(select userid, sum(total) as vSum
from UserResult
where bonustype <> '基本奖金'
group by userid
) as b, -- 先生成一个子表
UserResult as a -- 原来的表
where a.userid = b.userid
and b.vSum > 3000
and a.bonusType='补贴'
and a.qs=57
userid in (select userid from UserResult where bonustype <> '基本奖金' group by userid having sum(total)>=3000) 测试了一下。跟原来的速度一样,有时候可能还会更长一到两秒.TO:smart_zcg
qs、bonusType和userid建的索引,都是单列索引
bonustype <> '基本奖金' 基本是全表,
bonusType='补贴' 每天产生的,
qs=57 每天产生的
参考http://topic.csdn.net/u/20071130/14/d5603886-8786-4f99-bde1-f8b89fe43df6.html用一表变量来保存中间结果declare @tmp table (userid int)
insert into @tmp select userid from (select userid ,sum(total) vSum from UserResult where bonustype <> '基本奖金' group by userid) c where c.vSum> =3000;
select isnull(sum(total),0) from UserResult a where qs=57 and bonusType='补贴'
and userid in (select userid from @tmp)
再用w2jc给出的查询试试:
select isnull(b.vSum,0)
from
(select userid, sum(total) as vSum
from UserResult
where bonustype <> '基本奖金'
group by userid
) as b, -- 先生成一个子表
UserResult as a -- 原来的表
where a.userid = b.userid
and b.vSum > 3000
and a.bonusType='补贴'
and a.qs=57
select isnull(sum(total),0) from userresult a join
(select userid,sum(total) from userresult where bonustype<>'基本奖金'
group by userid having sum(total)>=3000) b
on a.qs=57 and a.bonustype='补贴' and a.userid=b.userid
from UserResult a
where qs=57
and bonusType='补贴'
and exists (
select 1
from (
select userid
from UserResult
where bonustype <> '基本奖金'
group by userid
having sum(total)>=3000) as b
where a.userid=b.userid)
FROM UserResult a INNER JOIN (SELECT userid FROM (SELECT userid
FROM UserResult
WHERE bonustype <> '基本奖金'
GROUP BY userid
HAVING sum(total) > 3000) b
GROUP BY b.userid) c
ON(a.userid = c.userid)
WHERE qs = 57 AND
bonusType = '补帖'
建议用JOIN来写
SELECT ISNULL(SUM(total),0)
FROM UserResult a
WHERE qs=57 and bonusType='补贴'
AND EXISTS(SELECT 1
FROM UserResult b WHERE a.userid=b.userid AND bonustype <> '基本奖金' GROUP BY userid HAVING SUM(total)>=3000)--如果是写在存储过程中,可以这样:
SELECT userid INTO #Tmp FROM UserResult WHERE bonustype <> '基本奖金' GROUP BY userid HAVING SUM(total)>=300
SELECT ISNULL(SUM(total),0)
FROM UserResult a
WHERE qs=57 and bonusType='补贴'
AND EXISTS(SELECT 1 FROM #Tmp WHERE userid=A.userid)
DROP TABLE #Tmp
userid in (select userid from UserResult where bonustype <> '基本奖金' group by userid having sum(total)>=3000) 不行分步运行
http://topic.csdn.net/u/20080227/14/8bce0844-bd15-42f0-9cda-a343d5d6601b.html?seed=2111206245
2、分条件的话可以考虑用 union all