易截截图软件、单文件、免安装、纯绿色、仅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包(package)学习笔记

包由包规范和包体两部分组成。
 
1、包规范(Package Specification)
包规范,也叫做包头,包含了有关包的内容的信息。但是,它不包含任何过程的代码。
创建包头的语法一般如下
 
CREATE [OR REPLACE] PACKAGE package_name {IS | AS}
Procedure_name | function_name | variable_declaration | type_def ......

sql server 内存表

--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 ......

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语句PART7

Merge statement
function benefits: 1) provides the ability to conditionally update, insert or delete data into a database table. 2) performs an update if the row exists, and an insert if it is a new row. --> 1) avoids seperate updates, 2) increase performance and ease of use. 3) is useful in dat ......

sql 数据库中不同数据库中两表创建视图

 CREATE VIEW MYVIEW
AS
   SELECT * from bjxxdiweb_database2007.dbo.bm_tongji
    UNION ALL
SELECT * from aa.DBO.chen
select * into aa..chen from bjxxdiweb_database2007.dbo.bm_tongji where 1=2
说明:数据库A的表的字段名必须和数据库B的表的字段名相同,包括数据类型等。 ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号