看了樓主的代碼,好象都是用SQL SERVER的思維來在ORACLE上做事,這太不好了,其實上面語句可以使用ORACLE的函數讓它寫得很簡單的,而且效率高。
今天沒時間寫了,下周一幫你寫。
今天沒時間寫了,下周一幫你寫。
解决方案 »
- 如何根据SQL_ID获取该SQL语句执行时间信息呢?
- 使用hint反倒会影响速度?
- 怎么查询一个字段中有没有繁体字?有的地区被设置为“台北”,有的地区被设置“臺北”。这样统计时很容易出错。
- 请教,如何查出数据库中所有有数据的表,和这些表的最后修改时间?
- 如何判断表中的数据未提交(在线等)?
- 大家用oracle9i,还是oracle10g多呀,
- oracle 为什么不能登陆 ORACLE MANAGEMENT SERVER?
- 各位老大帮帮我呀,很怪异的一个问题,真是太奇怪了,衷心感谢高手帮忙。
- 向各位高手请教一个复杂的sql查询问题。
- 如何查询某1天的数据
- 请问oracle 7.0 中的dept和emp示例表怎么在10中调出来啊?
- 如何在过程中调用操作系统命令
另外需要注意的就是在对主表进行数据关联的时候尽量避免全表扫描,适当使用优化策略
能不使用视图尽量不要使用视图,如果记录数太大的话视图的效率是非常低下的,可以考虑使用临时表
首先,第一个语句不需要与ws_planinfo表连接,若ws_send存在ws_planinfo不存在的plan_code就另说了。优化如下:
select ws_planinfo.plan_code,ws_planinfo.plan_numbers,a.send_flow,
decode(sign(a.num-ws_planinfo.plan_numbers),1,ws_planinfo.plan_numbers,a.num) num
from ws_planinfo,
(select plan_code,ws_send.send_flow,sum(ws_send.send_num) as num
from ws_send
group by plan_code,send_flow)a
where ws_planinfo.plan_code=a.plan_code 第一个语句一样,不要与ws_planinfo连接,
最后一个没有完全看明白,不过明显是一个错误的SQL,而且刊需要应该是两个SQL,而不是一个。
WITH q_View AS (
SELECT
t1.plan_code,max(t1.plan_numbers) plan_numbers,t2.send_flow,
max(t2.send_num) send_num,count(t3.repair_id) blooey_num--不能使用Count(t3.*)
FROM
ws_planinfo t1 JOIN ws_send t2 ON t1.plan_code=t2.plan_code
LEFT JOIN WS_REPAIR t3 ON t2.plan_code=t3.change_num and t2.send_flow=t3.send_flow
GROUP by t1.plan_code,t2.send_flow)
SELECT
PLAN_CODE,MAX(PLAN_NUMBERS) PLAN_NUMBERS,
SEND_FLOW,decode(SIGN(max(send_num)-MAX(PLAN_NUMBERS)),1,
MAX(PLAN_NUMBERS),Max(send_num)) NUM, --各工序實際生產數
max(blooey_num) BLOOEY_NUM ,--各工序故障数量
max(blooey_num)/decode(SIGN(max(send_num)-MAX(PLAN_NUMBERS)),1,
MAX(PLAN_NUMBERS),Max(send_num)) as flow_blooey_percent,--工序故障率
(sum(blooey_num) over(partion by plan_code))/MAX(PLAN_NUMBERS) as blooey_percent--批次故障率
FROM
q_View
GROUP BY PLAN_CODE,SEND_FLOW;以上語句,樓主可以直接拷入你的SQL*Plus中進行測試(或許因為沒有正確理解樓主的意思,我的語句可能會有錯),因為不知道你的Oracle的版本,所以沒有使用MODEl函數,如果使用MODEL函數,可能還更簡單些。
WITH q_View AS (
SELECT
t1.plan_code,max(t1.plan_numbers) plan_numbers,t2.send_flow,
max(t2.send_num) send_num,count(t3.repair_id) blooey_num
FROM
ws_planinfo t1 JOIN ws_send t2 ON t1.plan_code=t2.plan_code
LEFT JOIN WS_REPAIR t3 ON t2.plan_code=t3.change_num and t2.send_flow=t3.send_flow
GROUP by t1.plan_code,t2.send_flow)
SELECT
PLAN_CODE,MAX(PLAN_NUMBERS) PLAN_NUMBERS,
SEND_FLOW,decode(SIGN(max(send_num)-MAX(PLAN_NUMBERS)),1,
MAX(PLAN_NUMBERS),Max(send_num)) NUM, --各工序實際生產數
max(blooey_num) BLOOEY_NUM ,--各工序故障数量
max(blooey_num)/decode(SIGN(max(send_num)-MAX(PLAN_NUMBERS)),1,
MAX(PLAN_NUMBERS),Max(send_num)) as flow_blooey_percent,--工序故障率
(sum(max(blooey_num)) over (partion by plan_code))/MAX(PLAN_NUMBERS) as blooey_percent--批次故障率
FROM
q_View
GROUP BY PLAN_CODE,SEND_FLOW;上面語句已經過我的測試,能返回正確的結果,樓主可以使用一些典型數據進行測試。
你的语句好象有问题,另外
SELECT
t1.plan_code,max(t1.plan_numbers) plan_numbers,t2.send_flow,
max(t2.send_num) send_num,count(t3.repair_id) blooey_num
FROM
ws_planinfo t1 JOIN ws_send t2 ON t1.plan_code=t2.plan_code
LEFT JOIN WS_REPAIR t3 ON t2.plan_code=t3.change_num and t2.send_flow=t3.send_flow
GROUP by t1.plan_code,t2.send_flow这里好象也不符合要求啊,每个批次的数量求最大值没有意义,
当生产数量大于计划数量时,按计划数量为准(因存在返工,故生产数量有可能大于计划数量)
每个工序有可能传送多次,每个工序实际的生产数量也可能不同,
前工序生产,后工序可能还没有生产,这里的各工序是完成生产同一件产品的不同功能部分
所以要sum(send_num),不能max(send_num)
LEFT JOIN WS_REPAIR t3 ON t2.plan_code=t3.change_num and t2.send_flow=t3.send_flow
GROUP by t1.plan_code,t2.send_flow
另外你这里的join也有问题
WITH q_View AS (
SELECT
t1.plan_code,max(t1.plan_numbers) plan_numbers,t2.send_flow,
SUM(t2.send_num) send_num,count(t3.repair_id) blooey_num
FROM
ws_planinfo t1 JOIN ws_send t2 ON t1.plan_code=t2.plan_code
LEFT JOIN WS_REPAIR t3 ON t2.plan_code=t3.change_num and t2.send_flow=t3.send_flow
GROUP by t1.plan_code,t2.send_flow)
SELECT
PLAN_CODE,MAX(PLAN_NUMBERS) PLAN_NUMBERS,
SEND_FLOW,decode(SIGN(Max(send_num)-MAX(PLAN_NUMBERS)),1,
MAX(PLAN_NUMBERS),max(send_num)) NUM, --各工序實際生產數
max(blooey_num) BLOOEY_NUM ,--各工序故障数量
max(blooey_num)/decode(SIGN(max(send_num)-MAX(PLAN_NUMBERS)),1,
MAX(PLAN_NUMBERS),max(send_num)) as flow_blooey_percent,--工序故障率
(sum(max(blooey_num)) over (partion by plan_code))/MAX(PLAN_NUMBERS) as blooey_percent--批次故障率
FROM
q_View
GROUP BY PLAN_CODE,SEND_FLOW;至於你說的 join 處有問題,你能否說清楚些?我在SQL*Plus中測試過,應該沒問題!
WITH q_View AS (
SELECT
t1.plan_code,max(t1.plan_numbers) plan_numbers,t2.send_flow,
SUM(t2.send_num) send_num,count(t3.repair_id) blooey_num
FROM
ws_planinfo t1 ,ws_send t2,WS_REPAIR t3
Where t1.plan_code=t2.plan_code And t2.plan_code+=t3.change_num and t2.send_flow+=t3.send_flow
GROUP by t1.plan_code,t2.send_flow)
SELECT
PLAN_CODE,MAX(PLAN_NUMBERS) PLAN_NUMBERS,
SEND_FLOW,decode(SIGN(Max(send_num)-MAX(PLAN_NUMBERS)),1,
MAX(PLAN_NUMBERS),max(send_num)) NUM, --各工序實際生產數
max(blooey_num) BLOOEY_NUM ,--各工序故障数量
max(blooey_num)/decode(SIGN(max(send_num)-MAX(PLAN_NUMBERS)),1,
MAX(PLAN_NUMBERS),max(send_num)) as flow_blooey_percent,--工序故障率
(sum(max(blooey_num)) over (partion by plan_code))/MAX(PLAN_NUMBERS) as blooey_percent--批次故障率
FROM
q_View
GROUP BY PLAN_CODE,SEND_FLOW;如果Oracle8.1.7不能使用重用查詢,樓主可以將WITH後的語句先做成一個視圖,再運行後面的SELECT語句,如果不用使用分析函數,那就沒辦法了!!沉得樓主的Oracle版本太低了,至少應該升到Oracle9i
over (partion by plan_code) 这里也没看懂
over (partion by plan_code) 是分析函數的用法。另外,如果樓主的Oracle為10g的話,那可以使用模型函數,我的語句還可以大大簡化。
这里写错拉,应该如下:
over (partition by a.plan_code)另外
SELECT
t1.plan_code,max(t1.plan_numbers) plan_numbers,t2.send_flow,
SUM(t2.send_num) send_num,count(t3.repair_id) blooey_num
FROM
ws_planinfo t1 ,ws_send t2,WS_REPAIR t3
Where t1.plan_code=t2.plan_code And t2.plan_code+=t3.change_num and t2.send_flow+=t3.send_flow
GROUP by t1.plan_code,t2.send_flow这里统计的故障数目不对,
因为同一批次同一工序可以有多次传送生产的产品记录,这样一块故障表就会被重复统计。
批次 计划生产数量 状态
plan_code plan_numbers status(1表示未完工,2表示完工)2.物料传送记录表 ws_send
批次 工序 数量 状态
plan_code send_flow send_num status(1传送,2接收,3入库)
3.故障记录表
批次 送修工序 维修ID 类型
change_num give_proc repair_id code_type(0表示新投,1表示改制)
各工序故障表数量
create or replace view view_flow_blooey_num_plan as
(select WS_REPAIR.CHANGE_NUM AS PLAN_CODE,WS_REPAIR.GIVE_PROC as give_proc,
COUNT(WS_REPAIR.REPAIR_ID) AS blooey_num
from WS_REPAIR,WS_PLANINFO
where WS_REPAIR.CHANGE_NUM=WS_PLANINFO.PLAN_CODE and
WS_REPAIR.CODE_TYPE=0 and WS_PLANINFO.status='1'
group by WS_REPAIR.CHANGE_NUM,WS_REPAIR.GIVE_PROC)
各工序传送数量
CREATE OR REPLACE VIEW SEND_FLOW_COUNT AS
SELECT
t1.plan_code,max(t1.plan_numbers) plan_numbers,t2.send_flow,
SUM(t2.send_num) send_num
FROM
ws_planinfo t1 ,ws_send t2
Where t1.plan_code=t2.plan_code and
t2.status >='2'
and t1.status='1'
GROUP by t1.plan_code,t2.send_flow
求工序未生产完工批次故障率
SELECT
a.PLAN_CODE,MAX(a.PLAN_NUMBERS) PLAN_NUMBERS,
a.SEND_FLOW,decode(SIGN(Max(a.send_num)-MAX(a.PLAN_NUMBERS)),1,
MAX(a.PLAN_NUMBERS),max(a.send_num)) NUM, --各工序实际生产数量,当传送数量大于计划数 --量时,按计划数量为准(因存在返工,故传送数量有可能大于计划数量)
max(b.blooey_num) BLOOEY_NUM ,--各工序故障数量
max(b.blooey_num)/decode(SIGN(Max(a.send_num)-MAX(a.PLAN_NUMBERS)),1,
MAX(a.PLAN_NUMBERS),max(a.send_num)) as flow_blooey_percent,--工序故障率
(sum(max(b.blooey_num)) over (partition by a.plan_code))/MAX(a.PLAN_NUMBERS) as blooey_percent--批次故障率
FROM send_flow_count a,view_flow_blooey_num_plan b
where a.send_flow=b.give_proc and
a.plan_code=b.plan_code
GROUP BY a.PLAN_CODE,a.SEND_FLOW;
上面是我目前采用的办法