优化一条SQL语句

[aoyihuashao]

select ID,
(select WR_Name from Table1 where View1.ID = Table1.ID),
(select sum(Count) from View1 as T where T.Date <= '2007-12-04' AND T.ID = View1.ID) as START_COUNT,
(select sum(Count) from View1 as T where T.Date <= '2007-12-04' AND T.Date > '2007-10-1' AND SIGN = 1 AND T.ID = View1.ID ) as IN_COUNT,
(select sum(Count) from View1 as T where T.Date <= '2007-12-04' AND T.Date > '2007-10-1' AND SIGN = -1 AND T.ID = View1.ID ) as OUT_COUNT
from View1 where STATE= '2'
group by ID


目的：对VIEW1按ID进行统计，得出 Date <= '2007-12-04' 时间内的数量总和，Date <= '2007-12-04' AND Date > '2007-10-1' 时间内 SIGN = 1和-1的数量总和

[aoyihuashao]

目前这个语句 执行后提示：

未能为视图或函数解析分配辅助表。超过了查询中表的最大数目（260）。

[wangxuelid]

优化SQl2005里面有优化功能，在工具里面，，

[aoyihuashao]

优化SQl2005？？
什么东西？
没用过啊

[wangxuelid]

sql2005,采单工具拦，有一个优化的功能，，，，

[aoyihuashao]

没人能给处优化方法吗？

[rouqu]

select   ID, 
(select   WR_Name   from   Table1   where   View1.ID   =   Table1.ID), 
(select   sum(Count)   from     View1   as   T   where   T.Date   <=   '2007-12-04'   AND   T.ID   =   View1.ID)   as   START_COUNT, 
(select   sum(Count)   from     View1   as   T   where   T.Date   <=   '2007-12-04'   AND   T.Date   >   '2007-10-1'   AND   SIGN   =   1   AND   T.ID   =   View1.ID   )   as   IN_COUNT, 
(select   sum(Count)   from     View1   as   T   where   T.Date   <=   '2007-12-04'   AND   T.Date   >   '2007-10-1'   AND   SIGN   =   -1   AND   T.ID   =   View1.ID   )   as   OUT_COUNT 
from   View1   where   STATE=   '2' 
group   by   ID 

你的语句不规范

select 列名1,列名2,统计1(),统计2(),统计3() from view where <condition> group by 列名1,列名2

你的列名2却是一个select结果集 作为排错步骤 先将(select WR_Name from Table1 where View1.ID = Table1.ID)删除再运行

[aoyihuashao]

(select WR_Name from Table1 where View1.ID = Table1.ID),
这句不影响结果的。

去掉这句跟不去没什么区别，
关键是SUM(COUNT)那几句出错的。
两个SUM(COUNT)也不错，3个就连接太多了。

[rouqu]

这我就不清楚了 UP

[aoyihuashao]

过期结贴.

[mylover123]

用临时表处理吧，将视图放到临时表里面