一个很惨的问题:一表多查

[wea1978]

有这么个表tab:
有字段:

No code Anumeric Bnumeric Cnumeric Aname Bname Cname enginecode
a001 1 100 0 0 01 A1
a001 2 98 0 0 01 A2
a001 3 96 0 100 01 02 A2

每个no都有6至7个code,要的结果是
以enginecode来求每个阶段的平均不良数,每个阶段的不良数是这样求的:
比如上面 code =1 ,就没有,code=2就是
code=1的时候的Anumeric +Bnumeric+ Cnumeric-code=2的Anumeric +Bnumeric+ Cnumeric
结果可能只有一条:
code avgnumeric
A1 0.2

这还不算,还要以Aname里的某个或Bname 或Cname 的某个人来查

请各位大侠看看...还时候是比较惨啊...
谢谢!

[wea1978]

不好意思...我再说明一下...
比如有几天记录:
代码 顺序 A数量 B数量 C数量 A工号 B工号 C工号 机器名
a001 1 100 0 0 01 A1
a001 2 98 0 0 01 A2
a001 3 96 0 100 01 02 A2
要查:
机器为A2的平均不良数,不良数怎么得来的呢?就是在顺序那里找,比如顺序1,它的A,B,C数量之和是100,顺序2,数量就成了98了,那么这个顺序的不良呢就是(100-98)/100,后面的3,4,5,6,7都一样,每个顺序都看下一个顺序的数量,最后再要一个平均不良数，当然这个平均数是要包含有A2的
同样,如果查工号为01的不良数,也是同样的办法...

[wea1978]

当然顺序1因为是开始,就不存在不良数了.同样,最后一个也是不存在的...
还有就是,这些个顺序不一定是连着的,有可能是跳跃的,比如可能只有1,2,3,6或1,2,3,4,7

[dulei115]

select a.Cname as code, avg((a.numeric - b.numeric) * 1.0 / a.numeric) as avgnumeric
from (select code, Anumeric + Bnumeric + Cnumeric as numeric, Aname, Bname, Cname from tab) a
join (select code, Anumeric + Bnumeric + Cnumeric as numeric, Aname, Bname, Cname from tab) b
on b.code = (select min(code) from tab where code > a.code)
group by Cname

[wea1978]

请教一下:
select a.Cname as code 这句是为什么呢?它查的结果仅是cname列的吗?

[橡胶轮胎行业数字化高总]

分析一下

[wea1978]

to dulei115() ,麻烦看看,
出了个提示:
Server: Msg 209, Level 16, State 1, Line 16
Ambiguous column name 'Cname'.

谢谢!

[dulei115]

哦,看错了,我以为你那个A1是cname,原来是enginecode

[dulei115]

select a.enginecode as code, avg((a.numeric - b.numeric) * 1.0 / a.numeric) as avgnumeric
from (select code, Anumeric + Bnumeric + Cnumeric as numeric, enginecode from tab) a
join (select code, Anumeric + Bnumeric + Cnumeric as numeric, enginecode from tab) b
on b.code = (select min(code) from tab where code > a.code)
group by a.enginecode

[wea1978]

提示还是一样:
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'enginecode'.
我的代码是:
select a.enginecode as code, avg((a.nume - b.nume) * 1.0 / a.nume) as avgnumeric
from (select code, isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0) as nume, Aname, Bname, Cname,enginecode from #t_character_a) a
join (select code, isnull(Anumeric,0) + isnull(Bnumeric,0) + isnull(Cnumeric,0) as nume, Aname, Bname, Cname,enginecode from #t_character_a) b
on b.code = (select min(code) from #t_character_a where code > a.code)
group by enginecode

其中#t_character_a是个临时表,有数据的...

[wea1978]

to dulei115():
你后面那段可以执行,但得出的结果全部为NULL,不知为何...
请指教...

[wea1978]

哦,不好意思,我弄错了,没有除去空值,但也有这么个提示:
Server: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

[dulei115]

那就象你的那样全部加上isnull()试试
select a.enginecode as code, avg((a.numeric - b.numeric) * 1.0 / a.numeric) as avgnumeric
from (select code, isnull(Anumeric, 0) + isnull(Bnumeric, 0) + isnull(Cnumeric, 0) as numeric, enginecode from tab) a
join (select code, isnull(Anumeric, 0) + isnull(Bnumeric, 0) + isnull(Cnumeric, 0) as numeric, enginecode from tab) b
on b.code = (select min(code) from tab where code > a.code)
group by a.enginecode

[dulei115]

如果除数为0,你想要怎么处理????

[wea1978]

结果为0,那么在平均数时就只取0,只是多了个项目来平均而已...

[dulei115]

(a.numeric - b.numeric) * 1.0 / a.numeric
错误是这个地方出的,如果a.numeric是0,你想怎么样?让这个除的结果为0还是1

[dulei115]

avg((a.numeric - b.numeric) * 1.0 / a.numeric) as avgnumeric改为
avg(case a.numeric when 0 then 你想要的结果 else (a.numeric - b.numeric) * 1.0 / a.numeric end) as avgnumeric

[wea1978]

谢谢楼上..
得出的结果很多是负数...

[wea1978]

如果要处理负数应该怎么办呢?不想要出现负数,想把负数变成0

[dulei115]

avg(case when a.numeric = 0 then 0 when a.numeric > b.numeric then 0 else (a.numeric - b.numeric) * 1.0 / a.numeric end) as avgnumeric

[wea1978]

不错...可以实现,谢谢...
只是要把那个>变成<...
如果是查询某个aname或bname或cname也是一样吧?