先说明,没有数据库更改,建立等权限,只能查询
通过C#产生语句,然后查询,返回
经过验证,确实是sql运行语句过慢,请教各位,如何写可以更快些..谢谢~~分不够可以继续给~下面的查询语句为1个单位的查询,基本测试,4个单位耗时4秒,14个单位34秒,24个单位分别107和95秒.内部情况不太一样,66个单位18分钟7秒.C#和sqlplus中get sql的效率一样..
多个单位的语句我是在#中一个单位查询语句生成好后union另一个,直到最后一个,语句无错误下面为1个单位的语句,都用的代称,架构各位大大看懂后帮忙改改,说说,可接受时间内就好,结果<5秒可以多多给分~~select b.*,ROUND(EE*FF,2) as GG,ROUND(HH/II,2) as JJ,ROUND(EE/II,2) as KK,ROUND(EE/II*FF,2) as LL,ROUND(NN/EE,2) as MM,ROUND(((NN/EE*0.0026+1.185)*EE+OO),2) as PP,ROUND(b.RR/((NN/EE*0.0026+1.185)*EE+OO),2) as SS FROM
(
select AA, BB, ABCDE, DD, DDCX,substr(AA,5,2) as AF,
(
select MIN(hehe) from
(
select count(AA) OVER(order by AA) as hehe FROM
(
select * from 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as UU,
(
select MIN(hehe) from
(
select SUM(VV) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as VV,
(
select MIN(hehe) from
(
select count(AA) OVER(order by AA) as hehe FROM
(
select * from 表3 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as WW,
(
select to_char(last_day(to_date(AA,'BBCBBCmm')),'dd') from
(
select * from 表3 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
) as II,
(
select MIN(hehe) from
(
select SUM(EE+OO) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as HH,
(
select MIN(hehe) from
(
select SUM(EE) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as EE,
(
select MIN(hehe) from
(
select SUM(OO) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as OO,
(
select to_number(GGSC) as hehe from
(select * from 表4 WHERE AA='" + TT + "' and ABCDE='" + QQ + @"')
) as FF,PJRZ,
(
select ROUND(MIN(hehe),2) from
(
select AVG(CCD) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as CCD,
(
select ROUND(MIN(hehe),2) from
(
select AVG(CMD) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as CMD,RR,
(
select MIN(hehe) from
(
select SUM(OO) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as NN
from 表3
WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
) b
通过C#产生语句,然后查询,返回
经过验证,确实是sql运行语句过慢,请教各位,如何写可以更快些..谢谢~~分不够可以继续给~下面的查询语句为1个单位的查询,基本测试,4个单位耗时4秒,14个单位34秒,24个单位分别107和95秒.内部情况不太一样,66个单位18分钟7秒.C#和sqlplus中get sql的效率一样..
多个单位的语句我是在#中一个单位查询语句生成好后union另一个,直到最后一个,语句无错误下面为1个单位的语句,都用的代称,架构各位大大看懂后帮忙改改,说说,可接受时间内就好,结果<5秒可以多多给分~~select b.*,ROUND(EE*FF,2) as GG,ROUND(HH/II,2) as JJ,ROUND(EE/II,2) as KK,ROUND(EE/II*FF,2) as LL,ROUND(NN/EE,2) as MM,ROUND(((NN/EE*0.0026+1.185)*EE+OO),2) as PP,ROUND(b.RR/((NN/EE*0.0026+1.185)*EE+OO),2) as SS FROM
(
select AA, BB, ABCDE, DD, DDCX,substr(AA,5,2) as AF,
(
select MIN(hehe) from
(
select count(AA) OVER(order by AA) as hehe FROM
(
select * from 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as UU,
(
select MIN(hehe) from
(
select SUM(VV) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as VV,
(
select MIN(hehe) from
(
select count(AA) OVER(order by AA) as hehe FROM
(
select * from 表3 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as WW,
(
select to_char(last_day(to_date(AA,'BBCBBCmm')),'dd') from
(
select * from 表3 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
) as II,
(
select MIN(hehe) from
(
select SUM(EE+OO) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as HH,
(
select MIN(hehe) from
(
select SUM(EE) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as EE,
(
select MIN(hehe) from
(
select SUM(OO) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as OO,
(
select to_number(GGSC) as hehe from
(select * from 表4 WHERE AA='" + TT + "' and ABCDE='" + QQ + @"')
) as FF,PJRZ,
(
select ROUND(MIN(hehe),2) from
(
select AVG(CCD) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as CCD,
(
select ROUND(MIN(hehe),2) from
(
select AVG(CMD) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as CMD,RR,
(
select MIN(hehe) from
(
select SUM(OO) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
) as NN
from 表3
WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
) b
解决方案 »
- 大侠们,帮我看看下面那个oracle结果集 如何筛选
- ORA-00979: not a GROUP BY expression
- 把 group by b,a中的“a”去掉,但我必须保留select a,b,sum(qty)中的“a”,怎么写这个sql
- oracle 10g 打不开控制台
- WIN764wei ORACLE 11G R2 新建数据库实例报进程出现错误
- 关于oracle 10g 无法删除用户,session中不存在用户的会话
- 怎么把数据库里时间字段的日期部分值取出来啊
- OracleServer不能启动了,如何解决!高分求教!
- 如何删除dbms_job运行的一个计划任务???
- 如何实现"a001"这样的字段的自增啊?
- 面试题 如何将二叉树映射到表中?
- EXP-00008: 遇到 ORACLE 错误 942
然后通过别的表虚拟很多列出来, [第一次]
再通过虚拟出来的列继续虚拟计算出来的列 [第二次][第一次]虚拟:
select部分:
表1单独2次
表1,表2 union 6次 (可以简化么),一次select,多次as ....
表3 2次
表 4 1次我也想建立自己的view,貌似就会简单很多...select count()之前还有select min()是为了取一个值出来就好,select count()出来的是好多个一样的值,看说min速度快..基本情况如此...
天快亮了...没说清楚的...明天看回帖补充吧...不好意思..有点迷糊了...
ps2:oracle的缓冲蛮厉害的...同样的查询..第一次18分钟...第二次..<30秒...好玩~~
ps3:睡觉了.....拜托~~~~xinxin1982(新新)
总有可用分:9371 已花费可用分:1896 剩余可用分:7475
提供数据:
***********
结果数据:
***********你给的那些表,别人不知道要如果看?
SELECT TO_CHAR(LAST_DAY(TO_DATE(AA, 'BBCBBCmm')), 'dd')
FROM tt3) AS II,
(SELECT TO_NUMBER(GGSC) AS HEHE
FROM (SELECT *
FROM 表4
WHERE AA = '" + TT + "'
AND ABCDE = '" + QQ + @"')) AS FF,
这块代码如果返回记录数大于1条,就会报错。
是不是写错了。
我把重复的select写成过程?还是把上面的所有代码(一个单位的)的写成过程?大概如何架构和调用呢?简单的例子就好。。不用很详细谢谢。
还是把参数交给oracle去循环,产生结果,一次性返回?
(SELECT TO_CHAR(LAST_DAY(TO_DATE(AA, 'BBCBBCmm')), 'dd')
FROM tt3) AS II
一样还会出错啊。
大致看了一下,提供个思路:
能否把最里层的语句县合并计算了,然后在分组over什么的,这样避免多次的排序,换个思路或者好些呵呵
看样子你硬解析花的时间不少,不过也不至少相差这么大节
SELECT b.*, ROUND (ee * ff, 2) AS gg, ROUND (hh / ii, 2) AS jj,
ROUND (ee / ii, 2) AS kk, ROUND (ee / ii * ff, 2) AS ll,
ROUND (nn / ee, 2) AS mm,
ROUND (((nn / ee * 0.0026 + 1.185) * ee + oo), 2) AS pp,
ROUND (b.rr / ((nn / ee * 0.0026 + 1.185) * ee + oo), 2) AS ss
FROM (SELECT aa, bb, abcde, dd, ddcx, SUBSTR (aa, 5, 2) AS af,
(SELECT MIN (hehe)
FROM (SELECT COUNT (aa) OVER (ORDER BY aa) AS hehe
FROM (SELECT *
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS uu,
(SELECT MIN (hehe)
FROM (SELECT SUM (vv) OVER (ORDER BY aa) AS hehe
FROM (SELECT aa, bb, abcde, dd, ddcx, -1 AS abc,
abcd, abcde, abcdef, bac, bba, bbc,
bbd, bbe, bbf, bbg, cca, cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"
UNION
SELECT aa, bb, abcde, dd, ddcx, abc,
-1 AS abcd, -1 AS abcde, -1 AS abcdef,
-1 AS bac, -1 AS bba, bbc, bbd, bbe,
bbf, bbg, -1 AS cca, -1 AS cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表2
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS vv,
select b.*,ROUND(EE*FF,2) as GG,ROUND(HH/II,2) as JJ,ROUND(EE/II,2) as KK,ROUND(EE/II*FF,2) as LL,ROUND(NN/EE,2) as MM,ROUND(((NN/EE*0.0026+1.185)*EE+OO),2) as PP,ROUND(b.RR/((NN/EE*0.0026+1.185)*EE+OO),2) as SS FROM
(
select AA, BB, ABCDE, DD, DDCX,substr(AA,5,2) as AF,
(状况A) as UU,
(状况B) as VV,
(状况C) as WW,
(状况C) as II,
(状况B) as HH,
(状况B) as EE,
(状况B) as OO,
(状况D) as FF,PJRZ,
(状况B) as CCD,
(状况A) as CMD,RR,
(状况B) as NN
from 表3
WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
) b
select MIN(hehe) from
(
select count(**) OVER(order by AA) as hehe FROM
(
select * from 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
状况Aselect MIN(hehe) from
(
select SUM(**) OVER(order by AA) as hehe FROM
(
SELECT AA, BB, ABCDE, DD, DDCX, -1 as ABC, ABCD, ABCDE, ABCDEF, BAC, BBA,BBC, BBD, BBE, BBF, BBG, CCA, CMD, VV, EE, OO,NN, CCV, CCC, RCQL,CCD, CCE, CCF,CCG, LJABCDEFL,CCH, LVV, DDA
FROM 表1 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
union
SELECT AA, BB, ABCDE, DD, DDCX, ABC, -1 as ABCD, -1 as ABCDE, -1 as ABCDEF, -1 as BAC, -1 as BBA,BBC, BBD, BBE, BBF, BBG,-1 as CCA, -1 as CMD, VV, EE, OO, NN, CCV, CCC, RCQL, CCD,CCE,CCF,CCG, LJABCDEFL, CCH, LVV, DDA
FROM 表2 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)
状况B
select MIN(hehe) from
(
select count(**) OVER(order by AA) as hehe FROM
(
select * from 表3 WHERE AA='" + TT + "' and ABCDE='" + QQ + "' and DD<>concat(DDCX,'.1') and DD<>concat(DDCX,'.2') and " + oracle_temp + @"
)
)状况C
select to_number(GGSC) as hehe from
(select * from 表4 WHERE AA='" + TT + "' and ABCDE='" + QQ + @"')
状况D
状况A,B,C中每次不同的只有**部分以上一个完整的select为1个单位的查询,多个单位时候为union,好多大大说了用过程..不是很了解ing..能简单弄个例子么...最好是一个单位的select为一个过程?还是多个单位让oracle自动循环,实现union的效果?
SELECT b.*, ROUND (ee * ff, 2) AS gg, ROUND (hh / ii, 2) AS jj,
ROUND (ee / ii, 2) AS kk, ROUND (ee / ii * ff, 2) AS ll,
ROUND (nn / ee, 2) AS mm,
ROUND (((nn / ee * 0.0026 + 1.185) * ee + oo), 2) AS pp,
ROUND (b.rr / ((nn / ee * 0.0026 + 1.185) * ee + oo), 2) AS ss
FROM (SELECT aa, bb, abcde, dd, ddcx, SUBSTR (aa, 5, 2) AS af,
(SELECT MIN (hehe)
FROM (SELECT COUNT (aa) OVER (ORDER BY aa) AS hehe
FROM (SELECT *
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS uu,
(SELECT MIN (hehe)
FROM (SELECT SUM (vv) OVER (ORDER BY aa) AS hehe
FROM (SELECT aa, bb, abcde, dd, ddcx, -1 AS abc,
abcd, abcde, abcdef, bac, bba, bbc,
bbd, bbe, bbf, bbg, cca, cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"
UNION
SELECT aa, bb, abcde, dd, ddcx, abc,
-1 AS abcd, -1 AS abcde, -1 AS abcdef,
-1 AS bac, -1 AS bba, bbc, bbd, bbe,
bbf, bbg, -1 AS cca, -1 AS cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表2
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS vv,
(SELECT MIN (hehe)
FROM (SELECT COUNT (aa) OVER (ORDER BY aa) AS hehe
FROM (SELECT *
FROM 表3
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS ww,
(SELECT TO_CHAR (LAST_DAY (TO_DATE (aa, 'BBCBBCmm')),
'dd'
)
FROM (SELECT *
FROM 表3
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @")) AS ii,
(SELECT MIN (hehe)
FROM (SELECT SUM (ee + oo) OVER (ORDER BY aa) AS hehe
FROM (SELECT aa, bb, abcde, dd, ddcx, -1 AS abc,
abcd, abcde, abcdef, bac, bba, bbc,
bbd, bbe, bbf, bbg, cca, cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"
UNION
SELECT aa, bb, abcde, dd, ddcx, abc,
-1 AS abcd, -1 AS abcde, -1 AS abcdef,
-1 AS bac, -1 AS bba, bbc, bbd, bbe,
bbf, bbg, -1 AS cca, -1 AS cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表2
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS hh,
(SELECT MIN (hehe)
FROM (SELECT SUM (ee) OVER (ORDER BY aa) AS hehe
FROM (SELECT aa, bb, abcde, dd, ddcx, -1 AS abc,
abcd, abcde, abcdef, bac, bba, bbc,
bbd, bbe, bbf, bbg, cca, cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"
UNION
SELECT aa, bb, abcde, dd, ddcx, abc,
-1 AS abcd, -1 AS abcde, -1 AS abcdef,
-1 AS bac, -1 AS bba, bbc, bbd, bbe,
bbf, bbg, -1 AS cca, -1 AS cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表2
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS ee,
(SELECT MIN (hehe)
FROM (SELECT SUM (oo) OVER (ORDER BY aa) AS hehe
FROM (SELECT aa, bb, abcde, dd, ddcx, -1 AS abc,
abcd, abcde, abcdef, bac, bba, bbc,
bbd, bbe, bbf, bbg, cca, cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"
UNION
SELECT aa, bb, abcde, dd, ddcx, abc,
-1 AS abcd, -1 AS abcde, -1 AS abcdef,
-1 AS bac, -1 AS bba, bbc, bbd, bbe,
bbf, bbg, -1 AS cca, -1 AS cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表2
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS oo,
(SELECT TO_NUMBER (ggsc) AS hehe
FROM (SELECT *
FROM 表4
WHERE aa = '" + TT + "' AND abcde = '" + QQ + @"'))
AS ff,
pjrz,
(SELECT ROUND (MIN (hehe), 2)
FROM (SELECT AVG (ccd) OVER (ORDER BY aa) AS hehe
FROM (SELECT aa, bb, abcde, dd, ddcx, -1 AS abc,
abcd, abcde, abcdef, bac, bba, bbc,
bbd, bbe, bbf, bbg, cca, cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"
UNION
SELECT aa, bb, abcde, dd, ddcx, abc,
-1 AS abcd, -1 AS abcde, -1 AS abcdef,
-1 AS bac, -1 AS bba, bbc, bbd, bbe,
bbf, bbg, -1 AS cca, -1 AS cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表2
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS ccd,
(SELECT ROUND (MIN (hehe), 2)
FROM (SELECT AVG (cmd) OVER (ORDER BY aa) AS hehe
FROM (SELECT aa, bb, abcde, dd, ddcx, -1 AS abc,
abcd, abcde, abcdef, bac, bba, bbc,
bbd, bbe, bbf, bbg, cca, cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS cmd,
rr,
(SELECT MIN (hehe)
FROM (SELECT SUM (oo) OVER (ORDER BY aa) AS hehe
FROM (SELECT aa, bb, abcde, dd, ddcx, -1 AS abc,
abcd, abcde, abcdef, bac, bba, bbc,
bbd, bbe, bbf, bbg, cca, cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表1
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"
UNION
SELECT aa, bb, abcde, dd, ddcx, abc,
-1 AS abcd, -1 AS abcde, -1 AS abcdef,
-1 AS bac, -1 AS bba, bbc, bbd, bbe,
bbf, bbg, -1 AS cca, -1 AS cmd, vv, ee,
oo, nn, ccv, ccc, rcql, ccd, cce, ccf,
ccg, ljabcdefl, cch, lvv, dda
FROM 表2
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @"))) AS nn
FROM 表3
WHERE aa = '" + TT + "'
AND abcde = '" + QQ + "'
AND dd <> CONCAT (ddcx, '.1')
AND dd <> CONCAT (ddcx, '.2')
AND " + oracle_temp + @") b
格式化一下,一次还贴不上
不过如果要子查询的表名是动态的,就需要用到动态SQL了
给你看个我们取状态字的中文解释的函数
我们专门有张表解释各个状态字代表的含义
比如说订单表里有个订单状态
order_stat_cd 会出现几个值 10,20,30,90等等
在tb_zz005这张表会有以下数据
CD_ATTR_NM CD_VAL cd_val_desc
order_stat_cd 10 接单
order_stat_cd 20 出库
order_stat_cd 30 完成
order_stat_cd 90 取消
我们只要调用这个函数就可以取出状态字的解释
比如
select order_id,order_stat_cd,sf_cd_val_desc('order_stat_cd',order_stat_cd) stat
from order
出来的结果就是
order_id order_stat_cd stat
100001 10 接单
100002 20 出库
100003 90 取消
CREATE OR REPLACE Function sf_cd_val_desc (
as_cd_attr_nm IN tb_zz005.CD_ATTR_NM%type,
as_cd_val IN tb_zz005.CD_VAL%type
)
return varchar2
IS
ls_cd_attr_nm tb_zz005.CD_ATTR_NM%type;
ls_cd_val_desc tb_zz005.CD_VAL_DESC%type;
BEGIN select cd_val_desc
into ls_cd_val_desc
from tb_zz005
where cd_attr_nm = as_cd_attr_nm
and cd_val = as_cd_val; return ls_cd_val_desc;EXCEPTION
WHEN no_data_found THEN return '';
WHEN others THEN return to_char(sqlcode);
END sf_cd_val_desc;
/
是不是把所有可能的循环也写成函数(C),函数C根据次数循环调用B(B在调用A..),每个单位的变量值不一样..C#可以传递数组参数给oracle...么...(简单问
问...)..因为需要一次性返回所有结果...十分感谢..晚上好好试验看看..现在上班ing...
ps:函数和过程有什么区别呢?
问题比较多,不胜感激..~~
函数和存储过程有许多相似的,主要区别在于函数是有默认返回值(当然你可以不用)
存储过程只有通过传入OUT参数的变量,才能获得返回值函数里如果没有INSERT,UPDATE等语句,也就是说只有select的话,
那函数可以在select等语句里调用的存储过程不能在SELECT里调用
我的思路就是把多次运行的sql取min,改为运行一次取出所有的min,详细代码如下.没有环境不知有没有语法错误,思路肯定
是没有错误,我们的代码很多都是按照我的这个思路改得。WITH tt1 AS (SELECT AA,
BB,
ABCDE,
DD,
DDCX,
-1 AS ABC,
ABCD,
ABCDE,
ABCDEF,
BAC,
BBA,
BBC,
BBD,
BBE,
BBF,
BBG,
CCA,
CMD,
VV,
EE,
OO,
NN,
CCV,
CCC,
RCQL,
CCD,
CCE,
CCF,
CCG,
LJABCDEFL,
CCH,
LVV,
DDA
FROM 表1
WHERE AA = '" + TT + "'
AND ABCDE = '" + QQ + "'
AND DD <> CONCAT(DDCX, '.1')
AND DD <> CONCAT(DDCX, '.2')
AND " + oracle_temp + @"),
tt2 AS (SELECT AA,
BB,
ABCDE,
DD,
DDCX,
ABC,
-1 AS ABCD,
-1 AS ABCDE,
-1 AS ABCDEF,
-1 AS BAC,
-1 AS BBA,
BBC,
BBD,
BBE,
BBF,
BBG,
-1 AS CCA,
-1 AS CMD,
VV,
EE,
OO,
NN,
CCV,
CCC,
RCQL,
CCD,
CCE,
CCF,
CCG,
LJABCDEFL,
CCH,
LVV,
DDA
FROM 表2
WHERE AA = '" + TT + "'
AND ABCDE = '" + QQ + "'
AND DD <> CONCAT(DDCX, '.1')
AND DD <> CONCAT(DDCX, '.2')
AND " + oracle_temp + @"),
tt3 AS (SELECT *
FROM 表3
WHERE AA = '" + TT + "'
AND ABCDE = '" + QQ + "'
AND DD <> CONCAT(DDCX, '.1')
AND DD <> CONCAT(DDCX, '.2')
AND " + oracle_temp + @"),
xxx AS (SELECT MIN(UU) UU,ROUND(MIN(cmd), 2) cmd
FROM (SELECT COUNT(AA) OVER(ORDER BY AA) AS UU,
AVG(CMD) OVER(ORDER BY AA) AS cmd
FROM TT1),
yyy AS (SELECT MIN(WW) ww, MIN(II) II
FROM (SELECT COUNT(AA) OVER(ORDER BY AA) AS ww,
min(tO_CHAR(LAST_DAY(TO_DATE(AA, 'BBCBBCmm')), 'dd')) OVER(ORDER BY AA) AS ii
FROM TT3)) ,
ooo AS (SELECT TO_NUMBER(GGSC) AS ff
FROM (SELECT *
FROM 表4
WHERE AA = '" + TT + "'
AND ABCDE = '" + QQ + @"')) ,
zzz AS (SELECT MIN(VV) VV,
MIN(HH) HH,
MIN(EE) EE,
MIN(OO) OO,
ROUND(MIN(CCD), 2) CCD,
MIN(NN) NN
FROM (SELECT SUM(VV) OVER(ORDER BY AA) AS VV,
SUM(EE + OO) OVER(ORDER BY AA) AS HH,
SUM(EE) OVER(ORDER BY AA) AS EE,
SUM(OO) OVER(ORDER BY AA) AS OO,
AVG(CCD) OVER(ORDER BY AA) AS CCD,
SUM(OO) OVER(ORDER BY AA) AS NN
FROM (SELECT *
FROM TT1
UNION
SELECT * FROM TT2)))
SELECT B.*,
ROUND(EE * FF, 2) AS GG,
ROUND(HH / II, 2) AS JJ,
ROUND(EE / II, 2) AS KK,
ROUND(EE / II * FF, 2) AS LL,
ROUND(NN / EE, 2) AS MM,
ROUND(((NN / EE * 0.0026 + 1.185) * EE + OO), 2) AS PP,
ROUND(B.RR / ((NN / EE * 0.0026 + 1.185) * EE + OO), 2) AS SS
FROM (SELECT AA,
BB,
ABCDE,
DD,
DDCX,
SUBSTR(AA, 5, 2) AS AF,
PJRZ,
RR,
UU,
VV,
WW,
II,
FF,
HH,
EE,
OO,
CCD,
CMD,
NN
FROM 表3,xxx,yyy,ooo,zzz
WHERE AA = '" + TT + "'
AND ABCDE = '" + QQ + "'
AND DD <> CONCAT(DDCX, '.1')
AND DD <> CONCAT(DDCX, '.2')
AND " + oracle_temp + @") B;
再试验hebo2005的方法...先给分了.....
说明数据库设计的很烂