易截截图软件、单文件、免安装、纯绿色、仅160KB

SQL语句PART9

Group functions
SELECT [column,] group_function(column) ... from table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
e.g.:
SELECT department_id, job_id, SUM(salary), COUNT(employee_id) from employees GROUP BY department_id, job_id ;
SELECT [column,] group_function(column)... from table [WHERE condition] [GROUP BY group_by_expression]
[HAVING having_expression] [ORDER BY column];
GROUP BY with ROLLUP and CUBE Operators
1. Use ROLLUP or CUBE with GROUP BY to produce superaggregate rows by cross-referencing columns.
2. ROLLUP grouping produces a result set containing the regular grouped rows and the subtotal values.
3. CUBE grouping produces a result set containing the rows from ROLLUP and cross-tabulation rows.
SELECT [column,] group_function(column). . .from table [WHERE condition] [GROUP BY [ROLLUP] group_by_expression] [HAVING having_expression];[ORDER BY column];
//ROLLUP is an extension to the GROUP BY clause Use the ROLLUP operation to produce cumulative
aggregates, such as subtotals.
e.g. :
select nmoduleinfoid, ndocsortid, salary from t1;
1    1    1000
1    2    2100
1    3    1300
2    4    1400
select nmoduleinfoid, ndocsortid, sum(salary) from t1 group by rollup(nmoduleinfoid, ndocsortid);
1    1    1000
1    2    2100
1    3    1300
1          4400
2    4    1400
2          1400
            5800
SELECT [column,] group_function(column)... from table [WHERE condition] [GROUP BY [CUBE] group_by_expression] [HAVING having_expression] [ORDER BY column];
// CUBE is an extension to the GROUP BY clause. You can use the CUBE operator to produce cross-tabulation


相关文档:

ORACLE PL/SQL 对象(object)学习笔记(一)

 1、对象类型规范
 
创建对象类型规范的语法如下
 
CREATE [OR REPLACE] TYPE [schema.] type_name
[AUTHID {CURRENT_USER|DEFINER}] AS OBJECT (
Attribute1 datatype,
[attribute2 datatype,…]
[method 1]
[method 2]);
/

 
 
 
其中AUTHID指示将来执行该方法时, ......

SQL语句PART1

Oracle SQL(partI)
Data manipulation language(DML): select, insert, update, delete, merge.
Data definition language(DDL): create, alter, drop, rename, truncate, comment
Data control language(DCL): grant, revoke
Transaction control: commit, rollback, savepoint
Arithmetic Expressions:
+, -, *, / ......

SQL语句PART2

Subquery: (single-row subqueries and multi-rows subqueries).
select select_list
from table
where expr operator (select select_list from table);
single-row subqueries operator: =, >, >=, <, <=, <>
e.g.:
1. select department_id, min(salary) from employees group by department_id ......

SQL语句PART5

Confirming granted privileges
Data Dictionary View                Description
ROLE_SYS_PRIVS                System privileges granted to roles
ROLE_TAB_PRIVS    & ......

SQL语句PART8

PairWise subquery:
e.g.:
select * from wf_docsort where (ndocsortid,nmoduleinfoid)  in (select ndocsortid, nmoduleinfoid from wf_docsort where instr(cname,'文')>0)
the above sql is the same function as:
select * from wf_docsort where ndocsortid = (select ndocsortid from wf_docsort where ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号