易截截图软件、单文件、免安装、纯绿色、仅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


相关文档:

[转]SQL行列互换

问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果): 
姓名 语文 数学 物理 
---- ---- --- ......

06—PL/SQL编程

PL/SQL: Oracle中的SQL过程化编程语言
1. PL/SQL程序是由块结构构成,格式如下:
  [DECLARE
   --声明部分
 ]
  BEGIN
   -- 主体
   [EXCEPTION
     -- 异常处理块
   ]
  END;
  /  --块的结束
2.变量
简单变量: ......

SQL语句PART3

constraint Example:
1. grammer:
 create table [schema.]table
      (column datatype [DEFAULT expr]
     [column_constraint], ...
     [table_constraint] [,......]);
2. example of a column_level constraint:
create table empl ......

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的表的字段名相同,包括数据类型等。 ......

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号