Report writing and SQL statement writing

SQL statements have always been weak. The entire report has been difficult recently. A select statement can be written for two days. Sort out some common functions and writing methods here, and forget to take a look later (for Oracle databases ). The examples are all examples in production. They are saved directly. When we use them later, I copied them and used them directly. It seems a lot of effort. Let's stop looking at the examples. 1. recursive query Start with... Connect By is used to recursively traverse a table with a tree structure. Start with is followed By the initial value and Connect By is followed By the traversal condition. For example, SELECT ORG_ID FROM PC_ORGANIZATION start with ORG_ID IN ('hblqaibgob') connect by prior ORG_ID = PARENT_ID 2. NVL (eExpression1, eExpression2) returns a non-null value from two expressions. If eExpression1 is calculated as null, NVL () returns eExpression2. If the calculation result of eExpression1 is not null, eExpression1 is returned. EExpression1 and eExpression2 can be any data type. If both eExpression1 and eExpression2 are null values, NVL () returns NULL. For example, 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 ('20140901', 'yyyymmdd') and k. PROD_DATE <= TO_DATE ('20140901', '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 ('20140901', 'yyyymmdd') and k. PROD_DATE <= TO_DATE ('201312', 'yyyymmdd'), 1) RCQ2, FROM CD_WELL_SOURCE A, PC_ORGANIZATION_T B, PC_PRO_WELL_VOL_DAILY C WHERE C. WELL_ID =. WELL_ID and B. ORG_ID =. ORG_ID and. ORG_ID IN (SELECT ORG_ID FROM PC_ORGANIZATION start with ORG_ID IN ('hbase sedjiwe ') connect by prior ORG_ID = PARENT_ID) group by B. ORG_NAME) 3. The Round function rounds the specified value into the ROU. ND (number, [decimal_places]) parameter: number: preprocessing logarithm value. Decimal_places: number of decimal places retained after rounding. The example is the same as the Nvl function example. 4. Union, Union All, Intersect, Minus1, and Union operations are performed on the result set. Union operations are performed on the two result sets, excluding duplicate rows and sorting by default rules; 2. Union All: Perform Union operations on the two result sets, including duplicate rows without sorting. 3. Intersect: Perform intersection operations on the two result sets, excluding duplicate rows, sort the default rules at the same time. 4. Minus performs the Difference Operation on the two result sets, excluding duplicate rows, and sorts the default rules at the same time. Note: If you want to sort by Oder by, order by must be placed in the last set. For example: SELECT (case when ttt. WELL_COMMON_NAME is null then n' subtotal 'else TTT. orgname END) DW, WELL_COMMON_NAME, RQ, distance, DL, MD_LANDED, TOP_MD, PUMP_DIAMETER, distance, STROKE_LENGTH, distance, REVOLUTIONS, distance, CASING_PRES, BH_PRES, distance, distance, RCQ, LCQ, LCS, QDB, RFKQL, bz from (SELECT * FROM (select f. ORG_NAME ORGNAME, NULL WE LL_COMMON_NAME, ''rq, TO_NUMBER ('') PUMP_JACK_WORKING_TIME, ''dl, TO_NUMBER ('') MD_LANDED, TO_NUMBER ('') TOP_MD, TO_NUMBER ('') PUMP_DIAMETER, TO_NUMBER ('') Comment, 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_COLO R, 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 ('20140901', '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 ('20140901', '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, Objective C, Objective D, TEMP_WELL_MECH_ALL E, PC_ORGANIZATION_T F WHERE C. WELL_ID =. 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 ('20140901', 'yyyymmdd') and c. WELL_ID = E. WELL_ID (+) and c. PROD_DATE = E. TEST_DATE (+) and. 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 =. ORG_ID group by f. ORG_NAME union all select f. ORG_NAME ORGNAME,. WELL_COMMON_NAME, TO_CHAR (B. PROD_DATE, 'yyyymmdd') RQ, C. PU MP_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_ I D = 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, E. PUMP_EFFICIENCY, E. STROKE_LENGTH, E. STROKE _ FREQUENCY STROKE_FREQUENCY, D. REVOLUTIONS, D. HYDRAULIC_HEAD, D. CASING_PRES, D. 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, C. GAS_PROD_DAILY RCQ, C. WATER_PROD_DAILY RCS, (S Elect sum (GAS_PROD_DAILY) FROM PC_PRO_WELL_VOL_DAILY k where k. WELL_ID = C. WELL_ID and k. PROD_DATE <TO_DATE ('20140901', '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 ('20140901', '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 =. 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 ('20140901', 'yyyymmdd') and c. WELL_ID = E. WEL Rochelle ID (+) and c. PROD_DATE = E. TEST_DATE (+) and. 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 =. ORG_ID) temp order by temp. ORGNAME, TEMP. WELL_COMMON_NAME) TTT 5. conditional expression case when then else end. Syntax: CASEWHEN eExpression1 THEN Value1; WHEN eExpression2 THEN Value2; WHEN eExpression3 THEN Value3; ELSE Value4; END is similar to if else in the program. 6. Decode () function DECODE function comparison expression and search word. If match, corresponding result Syntax: DECODE (value, if1, then1, if2, then2, if3, then3 ,..., else), indicating that if the value is equal to if1, the result of the DECODE function returns then1 ,..., if it is not equal to any if value, else is returned. For example, select. 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 =. 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 ('20140901', 'yyyymmdd') AND c. well_id = e. well_id (+) AND c. prod_date = e. test_date (+) and. org_Id in ({OrgList}) order by c. well_id, c. prod_date