一个select语句优化

[xinxin1982]

先说明,没有数据库更改,建立等权限,只能查询
通过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

[xinxin1982]

加分成功,大家帮帮忙哦~~~~

[IAmXirour]

你的代码看的我头晕，能格式化一下么？
大致看了一下，提供个思路：
能否把最里层的语句县合并计算了，然后在分组over什么的，这样避免多次的排序，换个思路或者好些呵呵

[hebo2005]

ps2:oracle的缓冲蛮厉害的...同样的查询..第一次18分钟...第二次.. <30秒...好玩~~
看样子你硬解析花的时间不少,不过也不至少相差这么大节

[hebo2005]

/* Formatted on 2008/08/25 11:57 (Formatter Plus v4.8.8) */
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,

[xinxin1982]

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 + @"
)
)
状况A



select 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的效果?

[hebo2005]

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,
              

[hebo2005]

                  (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

格式化一下,一次还贴不上

[hebo2005]

你许多字段是用子查询实现的,而且粗看了下,大部分是差不多的,是可以写个函数,传入不同的参数,就能得出不同的结果,调用函数会比你这样快
不过如果要子查询的表名是动态的,就需要用到动态SQL了

[xinxin1982]

函数?....怎么用的...小弟oracle只会简单查询...

表名是固定的,就4个表,需要中...然后...函数怎么弄?可以提速么?...

楼上几位说的不用union用过程,楼上可以帮忙写个例子么?简单的就好..

上面贴出来的代码可能union 100多次...不一样的也就是C#生成的那几个TT,QQ,oracle_temp几个变量..

[xinxin1982]

66个单位控制在5秒内的查询速度,,,给500分.....~~~~~

[hebo2005]

给你看个我们取状态字的中文解释的函数
我们专门有张表解释各个状态字代表的含义
比如说订单表里有个订单状态
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;
/

[xinxin1982]

楼上的,十分感谢,晚上才能试验,谢谢你写的这么具体...

另外我想问一下...比方说我把一个单位里面的重复写成函数(A),然后把一个单位也写成函数(B),函数B中调用函数A...这样也可以吧..
是不是把所有可能的循环也写成函数(C),函数C根据次数循环调用B(B在调用A..),每个单位的变量值不一样..C#可以传递数组参数给oracle...么...(简单问
问...)..因为需要一次性返回所有结果...

十分感谢..晚上好好试验看看..现在上班ing...

[xinxin1982]

还有一个问题...比方说我这次创建的函数...就一直存在数据库中了?这个函数存活时间(lifetime)的问题...
ps:函数和过程有什么区别呢?
问题比较多,不胜感激..~~

[hebo2005]

是的,函数创建了,就在数据库里
函数和存储过程有许多相似的,主要区别在于

函数是有默认返回值(当然你可以不用)
存储过程只有通过传入OUT参数的变量,才能获得返回值

函数里如果没有INSERT,UPDATE等语句,也就是说只有select的话,
那函数可以在select等语句里调用的

存储过程不能在SELECT里调用

[wjlsmail]

Mark

[r_swordsman]

写出这么大的sql语句不是强，而是菜。
说明数据库设计的很烂

[r_swordsman]

如果现在还是开始阶段，重新设计数据库吧。

[SlaughtChen]

路过 呵呵

[xinxin1982]

楼上的我试验好了...但是....dataset不能包括with语句...晕了....
再试验hebo2005的方法...

先给分了.....

[xinxin1982]

还在摸索ing......白天不能干活...