oracleerp報表開發開發培訓教程-文庫吧

【正文】 ultiple records and group the results by one or more columns. The syntax for the GROUP BY clause is: SELECT column1, column2, ... column_n, aggregate_function (expression)FROM tablesWHERE predicatesGROUP BY column1, column2, ... column_n。 aggregate_function can be a function such as SUM, COUNT, MIN, or MAX. Example using the SUM function For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). SELECT department, SUM (sales) as Total salesFROM order_detailsGROUP BY department。 Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section. Having The HAVING clause is used in bination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns. The syntax for the HAVING clause is: SELECT column1, column2, ... column_n, aggregate_function (expression)FROM tables WHERE predicates GROUP BY column1, column2, ... column_nHAVING condition1 ... condition_n。 aggregate_function can be a function such as SUM, COUNT, MIN, or MAX. Example using the SUM function For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned. SELECT department, SUM (sales) as Total salesFROM order_details GROUP BY department HAVING SUM (sales) 1000 ORDER BY The ORDER BY clause allows you to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements. The syntax for the ORDER BY clause is: SELECT columns FROM tablesWHERE predicates ORDER BY column ASC/DESC。 The ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, the system assumed ascending order. ASC indicates ascending order. (default)DESC indicates descending order. Example 1 SELECT supplier_city FROM supplier WHERE supplier_name = 39。IBM39。 ORDER BY supplier_city。 This would return all records sorted by the supplier_city field in ascending order. Example 2 SELECT supplier_city FROM supplier WHERE supplier_name = 39。IBM39。 ORDER BY supplier_city DESC。 This would return all records sorted by the supplier_city field in descending order. UNION The UNION query allows you to bine the result sets of 2 or more select queries. It removes duplicate rows between the various select statements.Each SQL statement within the UNION query must have the same number of fields in the result sets with similar data types.The syntax for a UNION query is:select field1, field2, field_n from tablesUNIONselect field1, field2, field_n from tables UNION ALL The UNION ALL query allows you to bine the result sets of 2 or more select queries. It returns all rows (even if the row exists in more than one of the select statements).Each SQL statement within the UNION ALL query must have the same number of fields in the result sets with similar data types.The syntax for a UNION ALL query is:select field1, field2, field_n from tablesUNION ALLselect field1, field2, field_n from tables。 UPDATE The UPDATE statement allows you to update a single record or multiple records in a table. The syntax the UPDATE statement is: UPDATE table SET column = expression WHERE predicates INSERT The INSERT statement allows you to insert a single record or multiple records into a table. The syntax for the INSERT statement is: INSERT INTO table(column1, column2, ... columnn)VALUES(value1, value2, ... valuen) DELETE The DELETE statement allows you to delete a single record or multiple records from a tableThe syntax for the DELETE statement is: DELETE FROM table WHERE predicatespl/sql函數substr (string, start_position, [length])This function allows you to extract a substring from a stringFor Example: substr (39。This is a test39。, 6, 2) would return 39。is39。 substr (39。This is a test39。, 6) would return 39。is a test39。 substr (39。Tech on the Net39。, 1, 4) would return 39。Tech39。decode( expression , search , result [, search , result]... [, default] ) This function has the functionality of an IFTHENELSE statement For Example: SELECT supplier_name,decode (supplier_id, 10000,39。IBM39。, 10001, 39。Microsoft39。,10002, 39。Hewlett Packard39。, 39。Gateway39。) result FROM suppliersinstr(string1, string2, [start_position], [nth_appearance]) This function returns the location of a substring in a string For example:instr (39。Tech on the net39。, 39。e39。) would return 2。 the first occurrence of 39。e39。 instr (39。Tech on the net39。, 39。e39。, 1, 1) would return 2。 the first occurrence of 39。e39。 instr (39。Tech on the net39。, 39。e39。, 1, 2) would return 11。 the second occurrence of 39。e39。 instr (39。Tech on the net39。, 39。e39。, 1, 3) would return 14。 the third occurrence of 39。e39。 instr (39。Tech on the net39。, 39。e39。, 3, 2) would return 2Trim(text) This function removes leading and trailing spaces from a string For exampleTrim ( Tech on the Net) would return Tech on the Net Trim ( Alphabet ) would return AlphabetRTrim (text)This function removes trailing spaces from a stringFor example:RTrim (Tech on the Net ) would return Tech on the Net RTrim ( Alphabet ) would return Alphabet LTrim (text) This function removes leading spaces from a string For example:LTrim ( Tech on the Net) would return Tech on the Net LTrim ( Alphabet ) would return Alphabet to_number (string1, [format_mask], [nls_language] ) This function converts a string to a number For example:to_nu