SQL语句优化,SQL高手请进.(50分)

下面有两条SQL语句,作用是一样的.目标是用一条SQL语句把字段NUM1..NUM9中,相同的值的Series之差.例如
series num1
1 1
2 3
3 1
就是用SQL语句查出,Series为1时,Num1和上一次值为1时Series的差是多少,Series为2时,Num1的值3和上一次相同的值3的Series的差是多少,Series为3时,Num值同上一次值为1时Series差是多少
这个如果查出的结果是
Series Num1
1 Null
2 Null
3 2
我下面写了两个语句,结果得出是一样,但是运行时间有问题,一个是14秒,一个是45秒,但是数据库只有1850多数据.有没有高手可以写出更快的语句?因为如果数据多时,会很要命的.而且这个类似的语句有很多个表要用到.一起运行,十几分钟都通不过.
////////////////////////////////////////////////////////////////////////////
第一条语句
DELETE E_S_R_Number FROM E_S_R_Number,B_S,B_ChooseType
WHERE E_S_R_Number.Item = B_S.Item AND B_S.Type_ID = B_ChooseType.Type_ID
INSERT INTO E_S_R_Number(Item,Num1,Num2,Num3,Num4,Num5,Num6,Num7,Num8,Num9)
SELECT Item, A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num1 = D.Num1 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num2 = D.Num2 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num3 = D.Num3 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num4 = D.Num4 AND D.Type_id = F.Type_id ),
A.Series -(SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num5 = D.Num5 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num6 = D.Num6 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num7 = D.Num7 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num8 = D.Num8 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num9 = D.Num9 AND D.Type_id = F.Type_id )
FROM B_S A,B_ChooseType C
WHERE A.Type_id = C.Type_id
//////////////////////////////////////////////////////////////////////////////
第二条SQL语句
UPDATE E_S_R_Number
SET Num1 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num1 = D.Num1 AND D.Type_id = F.Type_id ),--1
Num2 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num2 = D.Num2 AND D.Type_id = F.Type_id ),--2
Num3 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num3 = D.Num3 AND D.Type_id = F.Type_id ),--3
Num4 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num4 = D.Num4 AND D.Type_id = F.Type_id ),--4
Num5 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num5 = D.Num5 AND D.Type_id = F.Type_id ),--5
Num6 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num6 = D.Num6 AND D.Type_id = F.Type_id ),--6
Num7 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num7 = D.Num7 AND D.Type_id = F.Type_id ),--7
Num8 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num8 = D.Num8 AND D.Type_id = F.Type_id ),--8
Num9 = A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num9 = D.Num9 AND D.Type_id = F.Type_id )--9
FROM B_S A,B_ChooseType C
WHERE A.Type_id = C.Type_id AND A.ITEM = E_S_R_Number.Item

有点不大理解楼主的意思，不知道这样行不行
select a.series,a.series-(select top 1 series from table where num1=a.num1 and series<a.series order by series desc)
from table a
order by series

我的第一条语句是先删除原来的数据,再重新插入数据.
第二条语句是在原来的基础上直接更新数据.
表中有一个Item字段,是关键字段.Series是Order by后的结果.
to stuwe:你的代码代入我的代码中,运行时间快了一倍.7秒完成一个过程.有没有更快的?因为数据库只有1850条数据,应该可以更快的.因为有上万条数据,甚至更多的话,程序运行会不可想像.

表该优化了
索引要建好

也许这样会快一些
select a.series,a.series-(select min(series) from table where num1=a.num1 and series<a.series)
from table a
order by series

to stuwe:
用MIN,MAX用的时间差不多,我之前用的是MAX,用TOP更快一点,如果是一条数据,如果只查NUM1,大家的时间都是1秒,如果是NUM1..NUM9,TOP的是7秒,MIN的是11秒,MAX的是14秒.当然这只是查询用的时间,不知道加上删除空数据,插入新查询出来的数据,会要增加多少时间,(更新语句不考虑了,太慢).现在用类似的代码一次性更新有10个表,常常会超时.而且我认为现在的数据太少了,运行仍需要要这么长时间,当数据量大时,不知道如何解决好.

我用临时表变量的方式进行测试，
生成2000行记录包括查询出来1秒钟就完成了
生成20000条记录包括查询出来就用了52秒

至于你说的1850条记录要用那么多时间，有点不可想象
也许你将数据存到临时表中，再进行查询会更好些

像这些:
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num2 = D.Num2 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num3 = D.Num3 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num4 = D.Num4 AND D.Type_id = F.Type_id ),
A.Series -(SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num5 = D.Num5 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num6 = D.Num6 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num7 = D.Num7 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num8 = D.Num8 AND D.Type_id = F.Type_id ),
A.Series - (SELECT MAX(D.Series) FROM B_S D,B_ChooseType F WHERE A.Series > D.Series AND A.Num9 = D.Num9 AND D.Type_id = F.Type_id
虽然可以得到结果,还是建议你使用临时表,这样查询实在不爽

总算看明白意思了，原来是有9个字段

测试了一下，2000行记录9个字段用max也才用了1秒钟(我个人机器)

重建索引或是直接重建表试试，如果是从多个表取数的，建议先将数据取到临时表再做查询

2000条数据一秒?
2000万条数据才52秒?
不会吧?简直是神速呀.
能把你建的那个表给我看看,参考一下吗?
lnming@yeah.net
你的电脑配置有多高?我的是2.4G,512M内存

select item,A.series-b.n1,A.series-b.n2,A.series-b.n3,A.series-b.n4,A.series-b.n5,A.series-b.n6,A.series-b.n7,A.series-b.n8,A.series-b.n9 FROM B_S A
inner join
(select
max(case when A.series>D.series and A.num1=D.num1 then D.series else null end) as n1
max(case when A.series>D.series and A.num2=D.num2 then D.series else null end) as n2
max(case when A.series>D.series and A.num3=D.num3 then D.series else null end) as n3
max(case when A.series>D.series and A.num4=D.num4 then D.series else null end) as n4
max(case when A.series>D.series and A.num5=D.num5 then D.series else null end) as n5
max(case when A.series>D.series and A.num6=D.num6 then D.series else null end) as n6
max(case when A.series>D.series and A.num7=D.num7 then D.series else null end) as n7
max(case when A.series>D.series and A.num8=D.num8 then D.series else null end) as n8
max(case when A.series>D.series and A.num9=D.num9 then D.series else null end) as n9
from B_S D) B on B.type_id=A.type_id
where exists (select * from B_choosetype C where A.Type_id = C.Type_id)

你原来的效率低,可能就是查询的次数太多.
上面是我的想法,你可以试一下.

语句通不过.

我用下面的语句测试,2000记录1秒就可以查出来,4000是4秒,6000是8秒,8000是13秒(这里时间指查询分析器下面显示的时间)
机器是CPU 1.86,内存512M

/*
create table test(
series int NULL,
num1 int NULL,
num2 int NULL,
num3 int NULL,
num4 int NULL,
num5 int NULL,
num6 int NULL,
num7 int NULL,
num8 int NULL,
num9 int NULL
)
*/

TRUNCATE TABLE test

declare @i int
declare @j int

set @i=6000
set @j=1

while (@i>0)
begin
insert into test values(@j,round(rand()*10,0),round(rand()*10,0),round(rand()*10,0),
round(rand()*10,0),round(rand()*10,0),round(rand()*10,0),round(rand()*10,0),
round(rand()*10,0),round(rand()*10,0))
set @j=@j+1
set @i=@i-1
end

select a.series,a.series-(select max(series) from test where num1=a.num1 and series<a.series) a,
a.series-(select max(series) from test where num2=a.num2 and series<a.series) b,
a.series-(select max(series) from test where num3=a.num3 and series<a.series) c,
a.series-(select max(series) from test where num4=a.num4 and series<a.series) d,
a.series-(select max(series) from test where num5=a.num5 and series<a.series) e,
a.series-(select max(series) from test where num6=a.num6 and series<a.series) f,
a.series-(select max(series) from test where num7=a.num7 and series<a.series) g,
a.series-(select max(series) from test where num8=a.num8 and series<a.series) h,
a.series-(select max(series) from test where num9=a.num9 and series<a.series) i
from test a
order by series

晕死了,原来需要分别14秒和45秒的语句,现在竟然只要1秒就可以完成,是怎么回事?
我原来的表什么都没有变动.

不知道这样改动,会不会在更多的电脑上有共同的稳定性?
DROP TABLE #TEMP
--先删除要更新表的对应内容,如果有的话.
DELETE E_S_R_Number FROM E_S_R_Number,B_S,B_ChooseType
WHERE E_S_R_Number.Item = B_S.Item AND B_S.Type_ID = B_ChooseType.Type_ID
--将需要用到的内容先写入临时表
SELECT B_S.* INTO #TEMP FROM B_S,B_CHOOSETYPE WHERE B_S.TYPE_ID = B_CHOOSETYPE.TYPE_ID
--根据临时表更新数据,这样就只操作一个表,应该好一点吧?至少不用像以往操作三个表以上
INSERT INTO E_S_R_Number(Item,Num1,Num2,Num3,Num4,Num5,Num6,Num7,Num8,Num9)
SELECT Item, A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num1 = D.Num1),
A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num2 = D.Num2),
A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num3 = D.Num3),
A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num4 = D.Num4),
A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num5 = D.Num5),
A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num6 = D.Num6),
A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num7 = D.Num7),
A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num8 = D.Num8),
A.Series - (SELECT MAX(D.Series) FROM #TEMP D WHERE A.Series > D.Series AND A.Num9 = D.Num9)
FROM #TEMP A

多人接受答案了。