做报表，写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
