SQL语句效率太低,2万条数5分钟还没有查询出来...
这是昨天的问题:
但是速度太慢了...
select distinct a.[No], a.code, a.codename,a.nume,(case when a.nume = 0 then 0 when a.nume < b.nume then 0 else (a.nume - b.nume) * 1.0 / a.nume end) as 不良
from (select [No], code, codename , (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume from V_work) a
join (select [Lot No], code, codename, (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume from V_work) b
on b.code = (select min(code) from V_work where code > a.code)
where a.[No] in(select distinct [No] from v_work where stdate between '2006-04-01' and '2006-04-10')
order by a.[No], a.code
请各位大侠帮忙看看,谢谢!
那个V_work是个简单的视图,只是从一个表里取相应的字段而已，应该没什么问题...
问题点数：100、回复次数：41Top
1 楼dulei115（前途无亮）回复于 2006-04-29 11:38:28 得分 10
这样
select distinct a.[No], a.code, a.codename,a.nume,
(case when a.nume = 0 then 0 when a.nume < b.nume then 0 else (a.nume - b.nume) * 1.0 / a.nume end) as 不良
from (select [No], code, codename , (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume from V_work
where stdate between '2006-04-01' and '2006-04-10') a
join (select [Lot No], code, codename, (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume from V_work) b
on b.code = (select min(code) from V_work where code > a.code)
order by a.[No], a.code
如果还是不行，就建code字段的索引Top
2 楼WangZWang（先来）回复于 2006-04-29 11:44:28 得分 5
首先要看你要得到什么样的结果，根据要求来写sql
单从你的sql不好写出更优化的sql.Top
3 楼wea1978（川）回复于 2006-04-29 11:45:38 得分 0
索引不能建,因为别人的数据库,所以只能在查询语句上作优化...Top
4 楼xeqtr1982（Visual C# .NET）回复于 2006-04-29 11:50:59 得分 5
可不可以把那段时间的记录取出来into到临时表，然后对临时表进行操作。Top
5 楼wea1978（川）回复于 2006-04-29 11:56:27 得分 0
要的结果就是对work表操作:
work表有字段:
[No], code, codename ,Anumeric,Bnumeric,Cnumeric,stdate
001 , 1 , products , 1000 , 0 , 0 , 2006-04-01
001 , 2 , products , 990 , 0 , 0 , 2006-04-01
001 , 3 , products , 990 , 0 , 0 , 2006-04-02
001 , 4 , products , 980 , 0 , 0 , 2006-04-02
001 , 5 , products , 0 , 0 , 0 , 2006-04-04
001 , 6 , products , 980 , 0 , 0 , 2006-04-04
要在此基础上得出每个code状态的不良,总共有7个状态,第1个和第7个状态的不良为0，
code=2的不良数是(1000-990)/1000,每个阶段都一样的做法,并且要排除数据为0或者下个阶段数据比上个阶段更多的情况...
刚才dulei115() 的方法查询出的数据有几百万条,而实际数只有1，2万或更少...
Top
6 楼WangZWang（先来）回复于 2006-04-29 12:01:33 得分 0
那你V_work的语句是什么？可不可以直接对work表调用？Top
7 楼wea1978（川）回复于 2006-04-29 12:01:45 得分 0
用临时表也可以,主要是能够得出结果，并且速度可以接受...
比如上面的查询得出来的结果应该是:
[No], code, codename ,Anumeric,Bnumeric,Cnumeric,stdate ,不良
001 , 1 , products , 1000 , 0 , 0 , 2006-04-01,0
001 , 2 , products , 990 , 0 , 0 , 2006-04-01,0.01
001 , 3 , products , 990 , 0 , 0 , 2006-04-02,0
001 , 4 , products , 980 , 0 , 0 , 2006-04-02,0
001 , 5 , products , 0 , 0 , 0 , 2006-04-04,0
001 , 6 , products , 980 , 0 , 0 , 2006-04-04,0
但如果code=6的数是970的话,它的不良就不是0了.而是(980-970)/980..同样code=4的时候也是,一直往下找,直到找不到比自己大的状态,就为0...,状态是固定最多7个,
Top
8 楼Yang_（扬帆破浪）回复于 2006-04-29 12:02:48 得分 30
临时表：
select [No], code, codename , (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume
into #a
from V_work
where stdate between '2006-04-01' and '2006-04-10'
select [Lot No], code, codename, (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume
into #b
from V_work --怎么没有where ？最好限定下
select distinct a.[No], a.code, a.codename,a.nume,(case when a.nume = 0 then 0 when a.nume < b.nume then 0 else (a.nume - b.nume) * 1.0 / a.nume end) as 不良
from #a a
join #b b
on b.code = (select min(code) from V_work where code > a.code)
--where a.[No] in(select distinct [No] from v_work where stdate between '2006-04-01' and '2006-04-10') --这个where 没有必要了
order by a.[No], a.code
Top
9 楼wea1978（川）回复于 2006-04-29 12:02:55 得分 0
可以直接对v_work表调用,但也是一样...因为v_work表只是个查询视图而已,
select [No], code, codename ,Anumeric,Bnumeric,Cnumeric,stdate from workTop
10 楼Yang_（扬帆破浪）回复于 2006-04-29 12:04:45 得分 0
尾巴：
drop table #a
drop table #b
Top
11 楼rouqu（石林#黄果树）回复于 2006-04-29 12:10:17 得分 0
....
on b.code = (select min(code) from V_work where code > a.code)
....
楼主能否解释一下这句话？看不明白Top
12 楼rouqu（石林#黄果树）回复于 2006-04-29 12:14:32 得分 0
连接的两个表都是相同的视图 楼主可以考虑能不能直接在一张表里进行处理
上面这个连接条件能不能理解成两个相同的视图之间做纪录对应 条件是隔行对应？Top
13 楼wea1978（川）回复于 2006-04-29 12:19:29 得分 0
不一定要用我的方式,呵呵...
我的方法也是dulei115() 朋友提供的
我只想以最快的方法得到结果...Top
14 楼wea1978（川）回复于 2006-04-29 12:31:20 得分 0
我的查询有问题:
现在用 Yang_(扬帆破浪) 的方法，共用去4分21秒,和dulei115()的方法使用的时间一样,结果也一样,服务器提示:
(5857 row(s) affected)
(5857 row(s) affected)
(1869253 row(s) affected)
请问有更好的方法吗?Top
15 楼wea1978（川）回复于 2006-04-29 13:00:12 得分 0
在where 后面再加多个and a.[No]=b.[No]就速度不会那么慢了,但结果还是不对,code只有1,2,3或者更少,这不是正确的结果,并且code=1的时候没有为0...Top
16 楼skywebnet（小苯）回复于 2006-04-29 13:02:10 得分 50
select distinct a.[No], a.code, a.codename,a.nume,
(case when a.nume = 0 then 0
when a.nume < a.bnume then 0 else (a.nume - a.bnume) * 1.0 / a.nume end) as 不良
from
(select [No], code, codename , (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume ,
(select top 1 isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0) as nume
from V_work where code>b.code order by code ) as bnum
from V_work b
where stdate between '2006-04-01' and '2006-04-10'
) a
order by a.[No], a.codeTop
17 楼wea1978（川）回复于 2006-04-29 13:08:55 得分 0
服务器提示:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'bnume'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'bnume'.Top
18 楼wea1978（川）回复于 2006-04-29 13:11:19 得分 0
哦...不好意思...我看错了...as bnum 改成as bnume就可以了...
但结果不正确...Top
19 楼wea1978（川）回复于 2006-04-29 13:13:38 得分 0
取一条结果:
A64002100 1 0M11 84517 .0000000000
A64002100 2 0M11 84486 .0000000000
A64002100 3 0M11 84254 .0000000000
A64002100 4 0M11 84528 1.0000000000
A64002100 6 0M11 84120 1.0000000000
Top
20 楼skywebnet（小苯）回复于 2006-04-29 13:24:33 得分 0
code=2的不良数是(1000-990)/1000 中 1000 ， 900 各是取的code ??的数值Top
21 楼wea1978（川）回复于 2006-04-29 13:34:26 得分 0
比如上面的code2的不良数是:
84517(上一个code即code1的数)-84486(code2的数)/84517(上一个code的数code1的数),结果即:
(84517-84486)/84517Top
22 楼Yang_（扬帆破浪）回复于 2006-04-29 13:48:03 得分 0
select distinct --看看这个distinct可不可以去掉？？
a.[No], a.code, a.codename,(isnull(a.Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0)) as nume,(case when (isnull(a.Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0)) = 0 then 0 when (a.isnull(Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0)) < (isnull(b.Anumeric,0) + isnull(b.Bnumeric,0) + isnull(b.Cnumeric,0)) then 0 else ((a.isnull(Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0)) - (isnull(b.Anumeric,0) + isnull(b.Bnumeric,0) + isnull(b.Cnumeric,0))) * 1.0 / (a.isnull(Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0)) end) as 不良
from work a
join work b
on b.code = (select min(code) from work where code > a.code)
and a.stdate between '2006-04-01' and '2006-04-10'
order by a.[No], a.code
在 work表加索引(code,[No])
Top
23 楼Yang_（扬帆破浪）回复于 2006-04-29 13:50:23 得分 0
条件是不是不够呀？
[No],codename分别什么含义？
Top
24 楼wea1978（川）回复于 2006-04-29 14:03:45 得分 0
条件是不够..
你那个结果出来是这样:
A64000200 1 TB5 78781 .0053312346
A64000200 2 TB5 78361 .0033307385
A64000200 3 TB5 78100 .0064020486
A64000200 4 TB5 77600 1.0000000000
A64000200 6 TB5 77560 1.0000000000
不是要这样的结果,正确的应该是:
[No], code, codename ,nume 不良
A64000200 1 TB5 78781 0
A64000200 2 TB5 78361 (78781-78361)/78781
A64000200 3 TB5 78100 (78361-78100)/78361
A64000200 4 TB5 77600 (78100-77600)/78100
A64000200 6 TB5 77560 (77600-77560)/77600
当然后面那个公式是要直接的结果的...
[No]指的是产品代号,和codename是一起的...code指的是每步操作...
Top
25 楼wea1978（川）回复于 2006-04-29 14:05:53 得分 0
最佳的结果是:
[No], code, codename ,nume 不良
A64000200 1 TB5 78781 0
A64000200 2 TB5 78361 (78781-78361)/78781
A64000200 3 TB5 78100 (78361-78100)/78361
A64000200 4 TB5 77600 (78100-77600)/78100
A64000200 5 TB5 0 0
A64000200 6 TB5 77560 (77600-77560)/77600
A64000200 7 TB5 0 0
Top
26 楼skywebnet（小苯）回复于 2006-04-29 14:12:17 得分 0
select a.[No], a.code, a.codename,a.nume,bnume,
isnull((case when a.nume = 0 then 0
when a.nume > a.bnume then 0 else (a.bnume - a.nume) * 1.0 / a.bnume end) ,0) as 不良
from
(select [No], code, codename , (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume ,
(select top 1 isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0) as nume
from V_work where code<b.code
order by code desc ) as bnume
from V_work b
where stdate between '2006-04-01' and '2006-04-10'
) a
order by a.[No], a.code
Top
27 楼wea1978（川）回复于 2006-04-29 14:26:43 得分 0
还是用上面那个来作比较:
A64002100 1 M11 84517 NULL .0000000000
A64002100 2 M11 84486 102033 .1719737731
A64002100 3 M11 84254 101567 .1704589088
A64002100 4 M11 84528 99812 .1531278804
A64002100 6 M11 84120 0 .0000000000
第1和第6个是对的...但2,3,4的结果还是不正确...
Top
28 楼Yang_（扬帆破浪）回复于 2006-04-29 14:42:20 得分 0
你原来语句错了
select a.[No], a.code, a.codename,(isnull(a.Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0)) as nume,(case when (isnull(a.Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0)) = 0 then 0 when (a.isnull(Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0)) > (isnull(b.Anumeric,0) + isnull(b.Bnumeric,0) + isnull(b.Cnumeric,0)) then 0 else ((b.isnull(Anumeric,0) + isnull(b.Bnumeric,0) + isnull(b.Cnumeric,0)) - (isnull(a.Anumeric,0) + isnull(a.Bnumeric,0) + isnull(a.Cnumeric,0))) * 1.0 / (b.isnull(Anumeric,0) + isnull(b.Bnumeric,0) + isnull(b.Cnumeric,0)) end) as 不良
from work a
left join work b --注意这里
on a.No=b.No --注意这里
and b.code = (select max(code) from work where code < a.code) --注意这里
where a.stdate between '2006-04-01' and '2006-04-10'
order by a.[No], a.code
在 work表加索引([No],code)
补号的再说
Top
29 楼wea1978（川）回复于 2006-04-29 14:47:41 得分 0
谢谢!
测试一下,不行的话考虑用游标吧...来每条都更新一下Top
30 楼wea1978（川）回复于 2006-04-29 14:58:04 得分 0
如果用left join,速度非常慢...如果用join,速度是快了,但没法比较,因为只取出有不良数的了.连code=1的基本数都没有取出来...
left join 只有5857条数, 运行超过5分钟...Top
31 楼Yang_（扬帆破浪）回复于 2006-04-29 14:59:01 得分 0
索引加了没
Top
32 楼Yang_（扬帆破浪）回复于 2006-04-29 15:00:33 得分 0
看来只有用update 的方法了。。。
我用的不熟
试试看吧
Top
33 楼Yang_（扬帆破浪）回复于 2006-04-29 15:10:00 得分 0
select [No], code, codename , (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume,cast(0 as numeric(10,8)) as [不良]
into #a
from V_work
where stdate between '2006-04-01' and '2006-04-10'
order by [No],code
declare @No varchar(20)
declare @code int
declare @nume numeric(10,2)
set @nume=0
set @=null
set @code=null
update #a
set
[不良]=case when [No]=@No and @nume<>0 and @nume>nume then (@nume-nume)/@nume
else 0
end,
@No=[No],@Code=Code,@nume=nume
select * from #a
drop table #a
--看看效果
Top
34 楼skywebnet（小苯）回复于 2006-04-29 15:26:25 得分 0
select a.[No], a.code, a.codename,a.nume,bnume,
isnull((case when a.nume = 0 then 0
when a.nume > a.bnume then 0 else (a.bnume - a.nume) * 1.0 / a.bnume end) ,0) as 不良
from
(select [No], code, codename , (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as nume ,
(select top 1 isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0) as nume
from V_work
where code<b.code
and stdate between '2006-04-01' and '2006-04-10' --这个范围需要添加？？？？
order by code desc ) as bnume
from V_work b
where stdate between '2006-04-01' and '2006-04-10'
) a
order by a.[No], a.codeTop
35 楼wea1978（川）回复于 2006-04-29 15:38:55 得分 0
[不良]的结果全部为0
我用了个游标:
select *,cast((0.001-0.001)as numeric(18,3)) as bl into #a from V_work where stdate between '2006-04-01' and '2006-04-10'
declare @sno char(9),@i int
set @i=1
DECLARE mycur CURSOR
FOR SELECT distinct [No]
FROM #a
OPEN mycur
FETCH NEXT FROM mycur INTO @sno
WHILE (@@FETCH_STATUS =0)
BEGIN
update #a set bl=(a.num-b.num)/a.num from
(select (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as num from #a where [No]=@sno and code=1) a,
(select (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as num from #a where [No]=@sno and code=2)b
where [No]=@sno and code=2
update #a set bl=(a.num-b.num)/a.num from
(select (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as num from #a where [No]=@sno and code=2) a,
(select (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as num from #a where [No]=@sno and code=3)b
where [No]=@sno and code=3
update #a set bl=(a.num-b.num)/a.num from
(select (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as num from #a where [No]=@sno and code=3) a,
(select (isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0)) as num from #a where [No]=@sno and code=4)b
where [No]=@sno and code=4
FETCH NEXT FROM mycur INTO @sno
END
CLOSE mycur
DEALLOCATE mycur
select * from #a order by [No], code
drop table #a
见笑了...这是个很笨的办法...Top
36 楼skywebnet（小苯）回复于 2006-04-29 15:45:56 得分 0
^-^ ,只要能按要求实现就OK了Top
37 楼wea1978（川）回复于 2006-04-29 15:46:35 得分 0
谢谢楼上各位...
skywebnet(小苯)的结果还是不正确...
谢谢了!看来我还是用那个游标了...但游标也会出问题
就是有条数据结果会为0,就出错了...Top
38 楼skywebnet（小苯）回复于 2006-04-29 15:51:11 得分 0
bl=case when a.num<>0 then (a.num-b.num)/a.num else 0 endTop
39 楼wea1978（川）回复于 2006-04-29 15:51:29 得分 0
奇怪,我在在查询的条件后面加多个条件 and a.num<>b.num,
服务器还是提示:
Server: Msg 8134, Level 16, State 1, Line 25
Divide by zero error encountered.
The statement has been terminated.
Top
40 楼wea1978（川）回复于 2006-04-29 15:56:42 得分 0
谢谢楼上...游标运行没有问题了...
谢谢各位朋友热情回复,再次感谢!
我对SQL掌握的还不够,还请各位大侠多多指教!Top
41 楼BurningM（沪指直下三千尺,疑是股民泪满天）回复于 2006-04-30 15:50:26 得分 0
where a.[No] in(select distinct [No] from v_work where stdate between '2006-04-01' and '2006-04-10')
---------------------
不要使用in，这会大幅度下降sql效率Top
