针对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
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秒 昏倒。。
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
呵呵,你的查询有错误哦
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
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
你的写法在第一次扫描wmsCounterAccount 表的时候就占用了64%的成本。
不合适
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
再看看呢
你的写法在第一次扫描wmsCounterAccount 表的时候就占用了64%的成本。
不合适像这样建索引(注意不要更改下面语句中栏位的顺序):
CREATE INDEX idx_wmsCounterAccount_01
ON wmsCounterAccount (date, cooid, counter)
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
再看看呢
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秒。
会死人的
你的语句多了一个分流汇总 查询时间56秒