如何提高该SQL语句的执行效率

[木头是猫]

SELECT cjhb, item, isnull(SUM(CASE WHEN cjcount > 0 AND 
              cjdate >= '2009-06-01 00:00:00' THEN cjcount END), 0) AS cjcount, 
              isnull(SUM(CASE WHEN cjcount = 0 AND 
              xrdate >= '2009-06-01 00:00:00' THEN 1 WHEN cjcount > 0 AND 
              xrdate >= '2009-06-01 00:00:00' AND 
              cjdate < '2009-06-01 00:00:00' THEN cjcount END), 0) AS cjcount2, 
              MAX(CASE WHEN cjcount > 0 THEN cjdate END) AS cjdate
        FROM bayhistory
        WHERE item <> ''
        GROUP BY item, cjhb
        HAVING SUM(CASE WHEN cjcount>0 and cjdate >= '2009-06-01 00:00:00' THEN cjcount END) > 0

环境：MSSQL2000 + WINXP , 百万级数据库

在SQL查询分析器运行时间为3秒，使用索引优化向导也没有索引建议

有什么方法可以提高这SQL语句的执行效率呢？

[rucypli]

WHERE item <> '' and cjcount>0 and cjdate >= '2009-06-01 00:00:00'
GROUP BY item, cjhb

[木头是猫]

[Quote=引用 1 楼 rucypli 的回复:]
WHERE item <> '' and cjcount>0 and cjdate >= '2009-06-01 00:00:00'
GROUP BY item, cjhb

[/Quote]

这样就无法统计出cjcount2

isnull(SUM(CASE WHEN cjcount = 0 AND 
              xrdate >= '2009-06-01 00:00:00' THEN 1 WHEN cjcount > 0 AND 
              xrdate >= '2009-06-01 00:00:00' AND 
              cjdate < '2009-06-01 00:00:00' THEN cjcount END), 0) AS cjcount2

[soft_wsx]

这么多嵌套！关注了！

[billpu]

帮顶