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语句的执行效率呢?
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语句的执行效率呢?
GROUP BY item, cjhb
这样就无法统计出cjcount2isnull(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