SQL语句效率太低,2万条数5分钟还没有查询出来...

Question

这样
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字段的索引

Answer 1

首先要看你要得到什么样的结果，根据要求来写sql
单从你的sql不好写出更优化的sql.

Answer 2

可不可以把那段时间的记录取出来into到临时表，然后对临时表进行操作。

Answer 3

要的结果就是对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万或更少...

Answer 4

那你V_work的语句是什么？可不可以直接对work表调用？

Answer 5

用临时表也可以,主要是能够得出结果，并且速度可以接受...
比如上面的查询得出来的结果应该是:
[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个,

Answer 6

临时表：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

Answer 7

可以直接对v_work表调用,但也是一样...因为v_work表只是个查询视图而已,
select [No],   code, codename   ,Anumeric,Bnumeric,Cnumeric,stdate from work

Answer 8

尾巴：drop table #a
drop table #b

Answer 9

....
on b.code = (select min(code) from V_work where code > a.code)
....楼主能否解释一下这句话？看不明白

Answer 10

不一定要用我的方式,呵呵...
我的方法也是dulei115() 朋友提供的
我只想以最快的方法得到结果...

Answer 11

我的查询有问题:
现在用 Yang_(扬帆破浪) 的方法，共用去4分21秒,和dulei115()的方法使用的时间一样,结果也一样,服务器提示:
(5857 row(s) affected)
(5857 row(s) affected)
(1869253 row(s) affected)
请问有更好的方法吗?

Answer 12

在where 后面再加多个and a.[No]=b.[No]就速度不会那么慢了,但结果还是不对,code只有1,2,3或者更少,这不是正确的结果,并且code=1的时候没有为0...

Answer 13

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.code

Answer 14

服务器提示:
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'.

Answer 15

哦...不好意思...我看错了...as bnum 改成as bnume就可以了...
但结果不正确...

Answer 16

取一条结果: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

Answer 17

code=2的不良数是(1000-990)/1000  中 1000 ， 900 各是取的code ??的数值

Answer 18

比如上面的code2的不良数是:
84517(上一个code即code1的数)-84486(code2的数)/84517(上一个code的数code1的数),结果即:
(84517-84486)/84517

Answer 19

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])

Answer 20

条件是不是不够呀？
[No],codename分别什么含义？

Answer 21

条件是不够..
你那个结果出来是这样:
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指的是每步操作...

Answer 22

最佳的结果是:
[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

Answer 23

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

Answer 24

还是用上面那个来作比较:
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的结果还是不正确...

Answer 25

你原来语句错了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)
补号的再说

Answer 26

如果用left join,速度非常慢...如果用join,速度是快了,但没法比较,因为只取出有不良数的了.连code=1的基本数都没有取出来...
left join 只有5857条数, 运行超过5分钟...

Answer 27

看来只有用update 的方法了
我用的不熟
试试看吧

Answer 28

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],codedeclare @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 #adrop table #a      
    
--看看效果

Answer 29

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.code

Answer 30

[不良]的结果全部为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=1DECLARE mycur CURSOR
FOR SELECT distinct [No]
         FROM #a
OPEN mycurFETCH 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 mycurselect * from #a order by [No], codedrop table #a见笑了...这是个很笨的办法...

Answer 31

谢谢楼上各位...
skywebnet(小苯)的结果还是不正确...
谢谢了!看来我还是用那个游标了...但游标也会出问题
就是有条数据结果会为0,就出错了...

Answer 32

bl=case when a.num<>0 then (a.num-b.num)/a.num else 0 end

Answer 33

奇怪,我在在查询的条件后面加多个条件 and a.num<>b.num,
服务器还是提示:
Server: Msg 8134, Level 16, State 1, Line 25
Divide by zero error encountered.
The statement has been terminated.

Answer 34

谢谢楼上...游标运行没有问题了...
谢谢各位朋友热情回复,再次感谢!
我对SQL掌握的还不够,还请各位大侠多多指教!

Answer 35

where a.[No] in(select distinct [No] from v_work where stdate between '2006-04-01' and '2006-04-10')
---------------------
不要使用in，这会大幅度下降sql效率