3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
优化一条SQL语句
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的数量总和
(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的数量总和
...全文
请发表友善的回复…
发表回复
mylover123 2009-11-12
用临时表处理吧，将视图放到临时表里面
aoyihuashao 2008-01-02
过期结贴.
rouqu 2007-12-05
这我就不清楚了 UP
aoyihuashao 2007-12-05
(select WR_Name from Table1 where View1.ID = Table1.ID),
这句不影响结果的。
去掉这句跟不去没什么区别，
关键是SUM(COUNT)那几句出错的。
两个SUM(COUNT)也不错，3个就连接太多了。
这句不影响结果的。
去掉这句跟不去没什么区别，
关键是SUM(COUNT)那几句出错的。
两个SUM(COUNT)也不错，3个就连接太多了。
rouqu 2007-12-05
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 2007-12-05
没人能给处优化方法吗？
wangxuelid 2007-12-04
sql2005,采单工具拦，有一个优化的功能，，，，
aoyihuashao 2007-12-04
优化SQl2005？？
什么东西？
没用过啊
什么东西？
没用过啊
wangxuelid 2007-12-04
优化SQl2005里面有优化功能，在工具里面，，
aoyihuashao 2007-12-04
目前这个语句 执行后提示：
未能为视图或函数解析分配辅助表。超过了查询中表的最大数目（260）。
未能为视图或函数解析分配辅助表。超过了查询中表的最大数目（260）。