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
相关文档:
因为要根据很复杂的规则处理用户数据,所以这里用到数据库的游标。平时不怎么用这个,写在这里纯粹为自己备个忘。
--将学籍号重复的放入临时表 tmp_zdsoft_unitive_code(除高中学段外)
drop table tmp_zdsoft_unitive_code;
select s.id ,sch.school_code,sch.school_name,s.student_name,s.unitive_code,s.identity_car ......
1、对象类型规范
创建对象类型规范的语法如下
CREATE [OR REPLACE] TYPE [schema.] type_name
[AUTHID {CURRENT_USER|DEFINER}] AS OBJECT (
Attribute1 datatype,
[attribute2 datatype,…]
[method 1]
[method 2]);
/
其中AUTHID指示将来执行该方法时, ......
oracle 方法 & ......
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 ......