导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

优化一条SQL语句

aoyihuashao 2007-12-04 03:44:36
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的数量总和
...全文
92 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
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个就连接太多了。
回复
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)。
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告