sql语句大优化，请高人进来指点

[coveking]

SELECT distinct a.cooid,b.sitename,b.siteurl from wmsCounterAccount
as a inner join wmsWapSiteInfo as b on a.cooid = b.uid
where sum(a.counter) = 0 and (a.date > = '2005-5-1 0:00:00'
and a.date <= '2005-10-31 23:59:59') and(b.updatedate > = '2005-5-1 0:00:00'
and b.updatedate <= '2005-5-31 23:59:59')
order by a.cooid desc

上面的语句报错“聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，并且要对其进行聚合的列是外部引用。”
如果把sum(a.counter)放到GROUP BY having子句做条件的话，将是一个很大的资源消费。请问过路牛人，上面的查询该如何写才是最好的效率，而且要包括错误的解决。－－sum(a.counter)的处理问题

[coveking]

针对SQL的报错，我将上面的SQL语句修改成为
SELECT distinct a.cooid,b.sitename,b.siteurl from wmsCounterAccount
as a inner join wmsWapSiteInfo as b on a.cooid = b.uid
where (a.date > = '2005-5-1 0:00:00'
and a.date <= '2005-10-31 23:59:59') and(b.updatedate > = '2005-5-1 0:00:00'
and b.updatedate <= '2005-5-31 23:59:59')
group by a.cooid,b.sitename,b.siteurl having sum(a.counter) = 0
order by a.cooid desc
查询了1分14秒 昏倒。。

[子陌红尘]

SELECT distinct
a.cooid,
b.sitename,
b.siteurl
from
wmsCounterAccount a
inner join
wmsWapSiteInfo as b
on
a.cooid = b.uid
where
(select sum(counter) from where cooid=a.cooid and ([date] between '2005-05-01 00:00:00' and '2005-10-31 23:59:59'))=0
and
(a.[date] between '2005-05-01 00:00:00' and '2005-10-31 23:59:59')
and
(b.updatedate between '2005-05-01 00:00:00' and '2005-05-31 23:59:59')
order by
a.cooid desc

[churchatp1]

看一下查询计划主要是什么地方耗时间

[子陌红尘]

效率不乐观

[coveking]

libin_ftsafe(子陌红尘)
呵呵，你的查询有错误哦。。。

[子陌红尘]

SELECT distinct
a.cooid,
b.sitename,
b.siteurl
from
wmsCounterAccount a
inner join
wmsWapSiteInfo as b
on
a.cooid = b.uid
where
(select sum(counter) from wmsCounterAccount where cooid=a.cooid and ([date] between '2005-05-01 00:00:00' and '2005-10-31 23:59:59'))=0
and
(a.[date] between '2005-05-01 00:00:00' and '2005-10-31 23:59:59')
and
(b.updatedate between '2005-05-01 00:00:00' and '2005-05-31 23:59:59')
order by
a.cooid desc

[coveking]

你的执行效率稍微好与我的语句，但是做为一条经常使用到的查询语句，执行20秒的话也还是不台符合要求了

[bugchen888]

用子查询:

SELECT DISTINCT b.uid,b.sitename,b.siteurl
FROM (SELECT a.cooid
FROM wmsCounterAccount a
WHERE a.date >= '2005-5-1 0:00:00'
AND a.date <= '2005-10-31 23:59:59'
GROUP BY a.cooid
HAVING SUM(a.counter)=0) a
INNER JOIN wmsWapSiteInfo b
ON a.cooid = b.uid
WHERE b.updatedate >= '2005-5-1 0:00:00'
AND b.updatedate <= '2005-5-31 23:59:59'
ORDER BY b.uid DESC

[coveking]

bugchen888(臭虫) :
你的写法在第一次扫描wmsCounterAccount 表的时候就占用了64%的成本。
不合适。。。

[Hs_Boy]

你可以在表wmsCounterAccount和wmsWapSiteInfo分别为字段date 和updatedate 和建立一个索引，就可以解决这个问题了。还有日期字段建议你用int型，你可以多放一个字段来建立操作的时间类型可以是datetime。

[Hs_Boy]

日期int型是说只记录8位年月日。convert(varchar,getdate(),112)

[duoluohuifeng]

SELECT distinct
a.cooid,
b.sitename,
b.siteurl
from
wmsCounterAccount a
inner join
wmsWapSiteInfo as b
on
a.cooid = b.uid
and
a.[date] between '2005-05-01 00:00:00' and '2005-10-31 23:59:59'
and
b.updatedate between '2005-05-01 00:00:00' and '2005-05-31 23:59:59'

where
(select sum(counter) from wmsCounterAccount where cooid=a.cooid and ([date] between '2005-05-01 00:00:00' and '2005-10-31 23:59:59'))=0
order by
a.cooid desc
再看看呢

[bugchen888]

bugchen888(臭虫) :
你的写法在第一次扫描wmsCounterAccount 表的时候就占用了64%的成本。
不合适。。。

像这样建索引(注意不要更改下面语句中栏位的顺序):
CREATE INDEX idx_wmsCounterAccount_01
ON wmsCounterAccount (date, cooid, counter)

[duoluohuifeng]

SELECT
a.cooid,
b.sitename,
b.siteurl
from
wmsCounterAccount a
inner join
wmsWapSiteInfo as b
on
a.cooid = b.uid
and
a.[date] between '2005-05-01 00:00:00' and '2005-10-31 23:59:59'
and
b.updatedate between '2005-05-01 00:00:00' and '2005-05-31 23:59:59'

where
(select sum(counter) from wmsCounterAccount where cooid=a.cooid and ([date] between '2005-05-01 00:00:00' and '2005-10-31 23:59:59'))=0
order by
a.cooid desc
group by a.cooid,
b.sitename,
b.siteurl
再看看呢

[mislrb]

学习

[liucan]

顶。。。。虽然不满懂！~

[coveking]

SELECT distinct a.cooid,b.sitename,b.siteurl,b.updatedate from wmsCounterAccount
as a inner join wmsWapSiteInfo as b on a.cooid = b.uid
where a.counter = 0 and (a.date <= '2005-7-31 23:59:59'
and a.date >= '2005-5-1 0:00:00') and(b.updatedate <= '2005-5-31 23:59:59'
and b.updatedate >= '2005-5-1 0:00:00' ) order by a.cooid desc

其实我们忽略了一个很重要的细节，最大条件应该最先在where中出现
a.date <= '2005-7-31 23:59:59'
and a.date >= '2005-5-1 0:00:00'
这样查我就用了25秒。

[coveking]

Hs_Boy(晃晃悠悠) 数据在俩万条以上就不要用函数，在5W条以上坚决不要用函数
会死人的。。。

[coveking]

duoluohuifeng(堕落回风) :
你的语句多了一个分流汇总 查询时间56秒