[原]做报表，写Sql语句的一点收获

Sql语句一直是弱项，最近整报表可是痛苦呀，一个select能写两天。

整理一下一些常用的函数和写法放在这，以后忘了来看看，呵呵（针对Oracle数据库）。

例子都是生产中的例子，直接放上来保存着，后面要用的时候，我就直接复制过去用了，看起来比较费劲，例子大家还是不要看了。

一、递归查询

    Start with...Connect By，用于对一个树形结构的表递归遍历，Start with后面跟初始值，Connect By后面跟遍历条件。

    比如：

   

SELECT ORG_ID
  FROM PC_ORGANIZATION
 START WITH ORG_ID IN ('HBlQAIBGob')
CONNECT BY PRIOR ORG_ID = PARENT_ID

 

二、NVL(eExpression1, eExpression2)从两个表达式返回一个非 null 值

如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回NULL。

比如：

SELECT DW, RCQ1, RCQ2, NVL(RCQ2, 0) - NVL(RCQ1, 0)
  FROM (SELECT DISTINCT B.ORG_NAME DW,
                        ROUND(SUM((SELECT AVG(GAS_PROD_DAILY)
                                    FROM PC_PRO_WELL_VOL_DAILY K
                                   WHERE K.WELL_ID = C.WELL_ID
                                     AND K.PROD_DATE >=
                                         TO_DATE('20080529', 'YYYYMMDD')
                                     AND K.PROD_DATE <=
                                         TO_DATE('20080829', 'YYYYMMDD'))),
                              1) RCQ1,
                        ROUND(SUM((SELECT AVG(GAS_PROD_DAILY)
                                    FROM PC_PRO_WELL_VOL_DAILY K
                                   WHERE K.WELL_ID = C.WELL_ID
                                     AND K.PROD_DATE >=
                                         TO_DATE('20080629', 'YYYYMMDD')
                                     AND K.PROD_DATE <=
                                         TO_DATE('20081029', 'YYYYMMDD'))),
                              1) RCQ2,
          FROM CD_WELL_SOURCE        A,
               PC_ORGANIZATION_T     B,
               PC_PRO_WELL_VOL_DAILY C
         WHERE C.WELL_ID = A.WELL_ID
           AND B.ORG_ID = A.ORG_ID
           AND A.ORG_ID IN (SELECT ORG_ID
                              FROM PC_ORGANIZATION
                             START WITH ORG_ID IN ('HBvsedjiwe')
                            CONNECT BY PRIOR ORG_ID = PARENT_ID)
         GROUP BY B.ORG_NAME)

 

三、Round 函数对指定数值进行四舍五入

    ROUND(number,[decimal_places])

参数：

number :预处理对数值。

decimal_places : 四舍五入后保留的小数位数。

示例同Nvl函数例子。

四、结果集集合操作 Union、Union All、Intersect、Minus

1、  Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

2、  Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

3、  Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

4、  Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

注意：如果要人为的用Oder by 排序，order by必须放在最后一个集合里面。

比如：

SELECT (CASE
         WHEN TTT.WELL_COMMON_NAME IS NULL THEN
          N'小计'
         ELSE
          TTT.orgname
       END) DW,
       WELL_COMMON_NAME,
       RQ,
       PUMP_JACK_WORKING_TIME,
       DL,
       MD_LANDED,
       TOP_MD,
       PUMP_DIAMETER,
       PUMP_EFFICIENCY,
       STROKE_LENGTH,
       STROKE_FREQUENCY,
       REVOLUTIONS,
       HYDRAULIC_HEAD,
       CASING_PRES,
       BH_PRES,
       START_FLUID_LEVEL_MD,
       WATER_QUALITY_COLOR,
       RCQ,
       LCQ,
       LCS,
       QDB,
       RFKQL,
       BZ
  FROM (SELECT *
          FROM (SELECT F.ORG_NAME ORGNAME,
                       NULL WELL_COMMON_NAME,
                       '' RQ,
                       TO_NUMBER('') PUMP_JACK_WORKING_TIME,
                       '' DL,
                       TO_NUMBER('') MD_LANDED,
                       TO_NUMBER('') TOP_MD,
                       TO_NUMBER('') PUMP_DIAMETER,
                       TO_NUMBER('') PUMP_EFFICIENCY,
                       TO_NUMBER('') STROKE_LENGTH,
                       TO_NUMBER('') STROKE_FREQUENCY,
                       TO_NUMBER('') REVOLUTIONS,
                       TO_NUMBER('') HYDRAULIC_HEAD,
                       TO_NUMBER('') CASING_PRES,
                       TO_NUMBER('') BH_PRES,
                       TO_NUMBER('') START_FLUID_LEVEL_MD,
                       '' WATER_QUALITY_COLOR,
                       SUM(C.GAS_PROD_DAILY) RCQ,
                       SUM(C.WATER_PROD_DAILY) RCS,
                       SUM((SELECT SUM(GAS_PROD_DAILY)
                             FROM PC_PRO_WELL_VOL_DAILY K
                            WHERE K.WELL_ID = C.WELL_ID
                              AND K.PROD_DATE <
                                  TO_DATE('20081013', 'yyyyMMdd') + 1)) LCQ,
                       SUM((SELECT SUM(WATER_PROD_DAILY)
                             FROM PC_PRO_WELL_VOL_DAILY K
                            WHERE K.WELL_ID = C.WELL_ID
                              AND K.PROD_DATE <
                                  TO_DATE('20081013', 'yyyyMMdd') + 1)) LCS,
                       SUM(C.GAS_PROD_DAILY -
                           (SELECT GAS_PROD_DAILY
                              FROM PC_PRO_WELL_VOL_DAILY K
                             WHERE K.WELL_ID = C.WELL_ID
                               AND K.PROD_DATE = C.PROD_DATE - 1)) QDB,
                       SUM(C.GAS_RELEASE_DAILY) RFKQL,
                       N'' BZ
                  FROM CD_WELL_SOURCE           A,
                       PC_DEV_WELL_ATTR         B,
                       PC_PRO_WELL_VOL_DAILY    C,
                       PC_PRO_WELL_STATUS_DAILY D,
                       TEMP_WELL_MECH_ALL       E,
                       PC_ORGANIZATION_T        F
                 WHERE C.WELL_ID = A.WELL_ID(+)
                   AND C.WELL_ID = B.WELL_ID(+)
                   AND C.WELL_ID = D.WELL_ID(+)
                   AND C.PROD_DATE = D.PROD_DATE(+)
                   AND C.PROD_DATE = TO_DATE('20081013', 'yyyyMMdd')
                   AND C.WELL_ID = E.WELL_ID(+)
                   AND C.PROD_DATE = E.TEST_DATE(+)
                   AND A.ORG_ID IN
                       (SELECT ORG_ID
                          FROM PC_ORGANIZATION
                         START WITH ORG_ID IN ('HBwoXhrt99')
                        CONNECT BY PRIOR ORG_ID = PARENT_ID)
                   AND F.ORG_ID = A.ORG_ID
                 GROUP BY F.ORG_NAME
                UNION ALL
                SELECT F.ORG_NAME ORGNAME,
                       A.WELL_COMMON_NAME WELL_COMMON_NAME,
                       TO_CHAR(B.PROD_DATE, 'YYYYMMDD') RQ,
                       C.PUMP_JACK_WORKING_TIME PUMP_JACK_WORKING_TIME,
                       DECODE(D.OIL_PROD_METHOD,
                              '1A',
                              D.UP_CURRENT || '/' || D.DOWN_CURRENT,
                              D.ELEC_PUMP_CURRENT_A) DL,
                       (SELECT DD.MD_LANDED
                          FROM CD_WELLBORE_T             AA,
                               CD_ASSEMBLY               BB,
                               CD_ASSEMBLY_COMP          CC,
                               CD_ASSEMBLY_COMP_STATUS_T DD
                         WHERE AA.WELL_ID = BB.WELL_ID
                           AND AA.WELLBORE_ID = BB.WELLBORE_ID
                           AND BB.WELL_ID = CC.WELL_ID
                           AND BB.WELLBORE_ID = CC.WELLBORE_ID
                           AND BB.ASSEMBLY_ID = CC.ASSEMBLY_ID
                           AND CC.WELL_ID = DD.WELL_ID
                           AND CC.WELLBORE_ID = DD.WELLBORE_ID
                           AND CC.ASSEMBLY_ID = DD.ASSEMBLY_ID
                           AND CC.ASSEMBLY_COMP_ID = DD.ASSEMBLY_COMP_ID
                           AND AA.WELL_ID = C.WELL_ID) MD_LANDED,
                       (SELECT TOP_MD
                          FROM CD_COMPLETION_T T
                         WHERE T.WELL_ID = C.WELL_ID
                           AND ROWNUM < 2) TOP_MD,
                       E.PUMP_DIAMETER PUMP_DIAMETER,
                       E.PUMP_EFFICIENCY PUMP_EFFICIENCY,
                       E.STROKE_LENGTH STROKE_LENGTH,
                       E.STROKE_FREQUENCY STROKE_FREQUENCY,
                       D.REVOLUTIONS REVOLUTIONS,
                       D.HYDRAULIC_HEAD HYDRAULIC_HEAD,
                       D.CASING_PRES CASING_PRES,
                       D.BH_PRES BH_PRES,
                       (SELECT Y.START_FLUID_LEVEL_MD
                          FROM CD_PRESSURE_SURVEY T, CD_PRESSURE_AWS Y
                         WHERE T.PRESSURE_SURVEY_ID = Y.PRESSURE_SURVEY_ID
                           AND C.WELL_ID = T.WELL_ID) START_FLUID_LEVEL_MD,
                       C.WATER_QUALITY_COLOR WATER_QUALITY_COLOR,
                       C.GAS_PROD_DAILY RCQ,
                       C.WATER_PROD_DAILY RCS,
                       (SELECT SUM(GAS_PROD_DAILY)
                          FROM PC_PRO_WELL_VOL_DAILY K
                         WHERE K.WELL_ID = C.WELL_ID
                           AND K.PROD_DATE <
                               TO_DATE('20081013', 'yyyyMMdd') + 1) LCQ,
                       (SELECT SUM(WATER_PROD_DAILY)
                          FROM PC_PRO_WELL_VOL_DAILY K
                         WHERE K.WELL_ID = C.WELL_ID
                           AND K.PROD_DATE <
                               TO_DATE('20081013', 'yyyyMMdd') + 1) LCS,
                       C.GAS_PROD_DAILY -
                       (SELECT GAS_PROD_DAILY
                          FROM PC_PRO_WELL_VOL_DAILY K
                         WHERE K.WELL_ID = C.WELL_ID
                           AND K.PROD_DATE = C.PROD_DATE - 1) QDB,
                       C.GAS_RELEASE_DAILY RFKQL,
                       C.REMARKS BZ
                  FROM CD_WELL_SOURCE           A,
                       PC_DEV_WELL_ATTR         B,
                       PC_PRO_WELL_VOL_DAILY    C,
                       PC_PRO_WELL_STATUS_DAILY D,
                       TEMP_WELL_MECH_ALL       E,
                       PC_ORGANIZATION_T        F
                 WHERE C.WELL_ID = A.WELL_ID(+)
                   AND C.WELL_ID = B.WELL_ID(+)
                   AND C.WELL_ID = D.WELL_ID(+)
                   AND C.PROD_DATE = D.PROD_DATE(+)
                   AND C.PROD_DATE = TO_DATE('20081013', 'yyyyMMdd')
                   AND C.WELL_ID = E.WELL_ID(+)
                   AND C.PROD_DATE = E.TEST_DATE(+)
                   AND A.ORG_ID IN
                       (SELECT ORG_ID
                          FROM PC_ORGANIZATION
                         START WITH ORG_ID IN ('HBwoXhrt99')
                        CONNECT BY PRIOR ORG_ID = PARENT_ID)
                   AND F.ORG_ID = A.ORG_ID
                
                ) TEMP
         ORDER BY TEMP.ORGNAME, TEMP.WELL_COMMON_NAME) TTT

 

五、条件表达式case when then else end。

语法：CASE

WHEN eExpression1 THEN Value1;
WHEN eExpression2 THEN Value2;
WHEN eExpression3 THEN Value3;
ELSE Value4;
END

类似与程序里面的if else，就不过说了，看四中队例子。

六、Decode()函数

DECODE函数比较表达式和搜索字，如果匹配，对应的结果

语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。

比如：

SELECT A.WELL_COMMON_NAME,
       TO_CHAR(B.PROD_DATE, 'YYYYMMDD'),
       C.PUMP_JACK_WORKING_TIME,
       decode(d.OIL_PROD_METHOD,
              '1A',
              D.UP_CURRENT || '/' || D.DOWN_CURRENT,
              d.ELEC_PUMP_CURRENT_A) dl,
  from CD_WELL_SOURCE           a,
       PC_DEV_WELL_ATTR         b,
       PC_PRO_WELL_VOL_DAILY    c,
       PC_PRO_WELL_STATUS_DAILY d,
       TEMP_WELL_MECH_ALL       e
  where c.Well_Id = a.Well_Id(+)
   and c.well_id = b.well_id(+)
   and c.well_id = d.well_id(+)
   and c.prod_date = d.prod_date(+)
   and c.prod_date = to_date('20081015', 'yyyyMMdd')
   AND c.well_id = e.well_id(+)
   AND c.prod_date = e.test_date(+)
   and a.Org_Id in ({OrgList})
 order by c.well_id, c.prod_date