这个sql语句可以优化下么？

[yue547283947]

select CLASS_ID,CLASS_NAME,CLASS_INFO,TOPIC_ID,CLASS_LASTTITLE,CLASS_LASTTIME,CLASS_LASTUSER,USER_TYPE,
(select count(TOPIC_FORUMID) FROM KY_FORUM_TOPIC where TOPIC_FORUMID=CLASS_ID)as aa,
(select count(TOPIC_FORUMID) FROM KY_FORUM_TOPIC where TOPIC_FORUMID=CLASS_ID
and TOPIC_TIME>=to_Date('2012-4-24 0:00:00','yyyy-mm-dd HH24:mi:ss')) as bb,
(select count(REPLY_ID) FROM KY_FORUM_REPLY where REPLY_CLASSID=CLASS_ID)as cc
FROM KY_FORUM_CLASS where CLASS_STATE=0 and CLASS_TYPE=1 order by CLASS_SORT

这个执行完毕2秒多。。。能不能再快点？

[ICE-word]

(SELECT COUNT(TOPIC_FORUMID)
FROM KY_FORUM_TOPIC
WHERE TOPIC_FORUMID = CLASS_ID) AS AA,
(SELECT COUNT(TOPIC_FORUMID)
FROM KY_FORUM_TOPIC
WHERE TOPIC_FORUMID = CLASS_ID
AND TOPIC_TIME >=
TO_DATE('2012-4-24 0:00:00', 'yyyy-mm-dd HH24:mi:ss')) AS BB,
(SELECT COUNT(REPLY_ID)
FROM KY_FORUM_REPLY
WHERE REPLY_CLASSID = CLASS_ID) AS CC



这里用case when 感觉会快点吧！！至少不用走3次全表

[loveofmylife]

KY_FORUM_CLASS 表永远只有4行
KY_FORUM_TOPIC 表和KY_FORUM_REPLY这个表可以考虑在TOPIC_FORUMID和REPLY_CLASSID上建立位图索引
sql上不知道怎么优化

[纯净水呐]

加索引试试

[yue547283947]

[Quote=引用 1 楼 的回复:]
加索引试试
[/Quote]

数据最多也就几千行。。。。KY_FORUM_CLASS 表永远只有4行。。。

这个要加索引？

[啊彪123]

select CLASS_ID,CLASS_NAME,CLASS_INFO,TOPIC_ID,CLASS_LASTTITLE,CLASS_LASTTIME,CLASS_LASTUSER,USER_TYPE,
 (select count(TOPIC_FORUMID) FROM KY_FORUM_TOPIC where TOPIC_FORUMID=CLASS_ID)as aa,
 (select count(TOPIC_FORUMID) FROM KY_FORUM_TOPIC where TOPIC_FORUMID=CLASS_ID  
 and TOPIC_TIME>=to_Date('2012-4-24 0:00:00','yyyy-mm-dd HH24:mi:ss')) as bb,
 (select count(REPLY_ID) FROM KY_FORUM_REPLY where REPLY_CLASSID=CLASS_ID)as cc
FROM KY_FORUM_CLASS where CLASS_STATE=0 and CLASS_TYPE=1 order by CLASS_SORT

select a.*,b.* FROM KY_FORUM_CLASS a,KY_FORUM_REPLY b where CLASS_STATE=0 and CLASS_TYPE=1 order by CLASS_SORT

使用这种方式。你上面的方式分3次遍历了大表，而且还放内存，所以慢！