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
相关文档：
SQL语句语法
目录
13.1. 数据定义语句
13.1.1. ALTER DATABASE语法
13.1.2. ALTER TABLE语法
13.1.3. CREATE DATABASE语法
13.1.4. CREATE INDEX语法
13.1.5. CREATE TABLE语法
13.1.6. DROP DATABASE语法
13.1.7. DROP INDEX语法
13.1.8. DROP TABLE语法
13.1.9. RENAME TABLE语法
13.2. 数据操作语� ......
基本方法:
UPDATETEXT { table_name.dest_column_name dest_text_ptr }//{ 要更新的表以及 text、ntext 或 image 列的名称,指向要更新的 text、ntext 或 image 数据的文本指针的值（由 TEXT ......
4、对象依赖性
CREATE OR REPLACE TYPE Obj1 AS OBJECT (
f1 NUMBER,
f2 VARCHAR2(10),
f3 DATE
);
/
CREATE OR REPLACE TYPE Obj2 AS OBJECT (
f1 DATE,
f2 CHAR(1)
);
/
CREATE OR REPLACE TYPE Obj3 AS OBJECT (
a Obj1,
b Obj2
);
/
OBJ3依赖于OBJ ......
--1加内存表
EXEC sp_tableoption '表名','pintable', 'true'
--2卸载内存表
EXEC sp_tableoption '表名','pintable', 'false'
--2查询是否有内存表驻留
SELECT * from INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROP ......
Differring Constraints:
Constraints can have the following attributes: DEFFERRABLE / NOT DEFFERRABLE, INITIALLY DEFFERRED / INITIALLY IMMEDIATE.
e.g.:
alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred; // deferring constraint on creation. ......
