金沙国际平台登录-金沙国际会员登录

热门关键词: 金沙国际平台登录,金沙国际会员登录

sql的max之类的聚合函数只能针对同一列的n行运算

sql的max之类的聚合函数只能针对同一列的n行运算,如果对n列运算,一般都用case 语句来判断,如果列少还比较容易写,列多了就麻烦了。--------------------------------------------------------------------------------/* 测试名称:利用 XML 求任意列之间的聚合 测试功能:对一张表的列数据做 min 、 max 、 sum 和 avg 运算 运行原理:字段合并为 xml 后做 xquery 查询转为行集后聚合 */ -- 建立测试环境 declare @t table ( id smallint , a smallint , b smallint , c smallint , d smallint , e smallint , f smallint ) insert into @t select 1, 1, 2, 3, 4, 6, 7 union all select 2, 34, 45, 56, 54, 9, 6 -- 测试语句 select a.*, c.* from @t a outer apply( select doc=( select * from @t as doc where id= a. id for xml path ( '' ), type ) ) b outer apply( select min ( r) as minValue, max ( r) as maxValue, sum ( r) as sumValue, avg ( r) as avgValue from ( select cast ( cast ( d. n. query( 'text()' ) as varchar ( max )) as int ) as r from doc. nodes( '/a,b,c,d,e,f' ) D( n)) tt ) c /* 测试结果 id a b c d e f minValue maxValue sumValue avgValue

oracle的伪列以及伪表


   oracle系统为了实现完整的关系数据库功能,系统专门提供了一组成为伪列(Pseudocolumn)的数据库列,这些列不是在建立对象时由我们完成的,而是在我们建立时由Oracle完成的。Oracle目前有以下伪列:

----------- ----------- 1 1 2 3 4 6 7 1 7 23 3 2 34 45 56 54 9 6 6 56 204 34 */

一、伪列:

  CURRVAL AND NEXTVAL 使用序列号的保留字

  LEVEL 查询数据所对应的层级

  ROWID 记录的唯一标识

  ROWNUM 限制查询结果集的数量
二、伪表

DUAL 表

该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。

一般用于验证函数。例如:

select sysdate,to_char(sysdate,'yyyy-mm-dd HH24:mm:ss') from dual

 

Oracle伪列RowID

一、什么是伪列RowID?
1、首先是一种数据类型,唯一标识一条记录物理位置的一个id,基于64位编码的18个字符显示。
2、未存储在表中,可以从表中查询,但不支持插入,更新,删除它们的值。

二、RowID的用途
1,在开发中使用频率应该是挺多的,特别在一些update语句中使用更加频繁。所以oracle ERP中大部份的视图都会加入rowid这个字段。
   在一些cursor定义时也少不了加入rowid。但往往我们在开发过程中,由于连接的表很多,再加上程序的复制,有时忽略了rowid对应的是那一个表中rowid,所以有时过程出错,
   往往花上很多时间去查错,最后查出来既然是update时带的rowid并非此表的rowid,所以在发现很多次的错误时,重视rowid起来了,开发中一定要注意rowid的匹配
2,能以最快的方式访问表中的一行。
3,能显示表的行是如何存储的。
4,作为表中唯一标识。

三,RowID的组成
rowid确定了每条记录是在Oracle中的哪一个数据对象,数据文件、块、行上。
ROWID 的格式如下:

 数据对象编号        文件编号        块编号            行编号
   OOOOOO             FFF                BBBBBB    RRR
  由 data_object_id# + rfile# + block# + row#   组成,占用10个bytes的空间,
    32bit的 data_object_id#,
    10 bit 的 rfile#, 
    22bit 的 block#,
    16 bit 的 row#. 
   所以每个表空间不能超过1023个 数据文件。

四、RowID的应用
1。准备数据:
   当试图对库表中的某一列或几列创建唯一索引时,
   系统提示 ORA-01452 :不能创建唯一索引,发现重复记录。
    ,id,name from (

select * from student order by name

);
    ROWNUM ID     NAME


         1 200003 李三
         2 200002 王二
         3 200001 张一
         4 200004 赵四
这样就成了按name排序,并且用rownum标出正确序号(有小到大)
笔者在工作中有一上百万条记录的表,在jsp页面中需对该表进行分页显示,便考虑用rownum来作,下面是具体方法(每页显示20条):
“select * from tabname where rownum<20 order by name" 但却发现oracle却不能按自己的意愿来执行,而是先随便取20条记录,然后再order by,后经咨询oracle,说rownum确实就这样,想用的话,只能用子查询来实现先排序,后rownum,方法如下:
"select * from (select * from tabname order by name) where rownum<20",但这样一来,效率会低很多。
后经笔者试验,只需在order by 的字段上加主键或索引即可让oracle先按该字段排序,然后再rownum;方法不变:   

“select * from tabname where rownum<20 order by name"

取得某列中第N大的行
select column_name from (

select table_name.*,dense_rank() over (order by column desc) rank

from table_name

)

where rank = &N;

 

假如要返回前5条记录:
select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)

假如要返回第5-9条记录:
select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
选出结果后用name排序显示结果。(先选再排序)

注意:只能用以上符号(<、<=、!=)。

 

select * from tablename where rownum != 10;返回的是前9条记录。
不能用:>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件不成立。

另外,这个方法更快:
select * from (

select rownum r,a from yourtable where rownum <= 20

order by name

)

where r > 10

这样取出第11-20条记录!(先选再排序再选)

 

要先排序再选则须用select嵌套:内层排序外层选。
rownum是随着结果集生成的,一旦生成,就不会变化了;同时,生成的结果是依次递加的,没有1就永远不会有2!
rownum 是在查询集合产生的过程中产生的伪列,并且如果where条件中存在 rownum 条件的话,则:

1: 假如判定条件是常量,则:
只能 rownum = 1, <= 大于1 的自然数, = 大于1 的数是没有结果的;大于一个数也是没有结果的
即 当出现一个 rownum 不满足条件的时候则 查询结束 this is stop key(一个不满足,系统将该记录过滤掉,则下一条记录的rownum还是这个,所以后面的就不再有满足记录,this is stop key);

2: 假如判定值不是常量,则:
若条件是 = var , 则只有当 var 为1 的时候才满足条件,这个时候不存在 stop key ,必须进行full scan ,对每个满足其他where条件的数据进行判定,选出一行后才能去选rownum=2的行……

以下摘自《中国IT实验室》

1.在oracle中实现select top n

   由于oracle不支持select top语句,所以在oracle中经常是用order by跟rownum的组合来实现select top n的查询。

简单地说,实现方法如下所示:

select 列名1...列名n from   
(select 列名1...列名n from 表名 order by 列名1...列名n)
where rownum<=n(抽出记录数)
order by rownum asc

   下面举个例子简单说明一下。

顾客表customer(id,name)有如下数据:

ID NAME

   01 first

   02 Second

   03 third

   04 forth

   05 fifth

   06 sixth

   07 seventh

   08 eighth

   09 ninth

   10 last

   则按NAME的字母顺抽出前三个顾客的SQL语句如下所示:

select * from

   (select * from customer order by name)

   where rownum<=3

   order by rownum asc

   输出结果为:

   ID NAME

   08 eighth

   05 fifth

   01 first

序列

       可以保证多个用户对同一张表进行操作时生成唯一的整数,通常用来做表的主键。

创建序列:

       create sequence <序列名字>

       start with  <起始值>

金沙国际会员登录 ,       increment by  <增长值>

       [MaxValue  <最大值>]

       [NoMaxValue] //没有上限

       例如:

              create sequence  mySeq

                     start with 1

                     increment 1

 

删除序列:

       drop sequence <序列名字>

修改序列:

       alter sequence <序列名字>

              [start with  <起始值>]

       [increment by  <增长值>]

       [MaxValue  <最大值>]

查看序列:

       使用下列视图之一:

              Dba_Sequences

       All_ Sequences
              User_ Sequences

访问序列:

       CurVal 返回序列的当前值

       NextVal 返回序列的下一个值

例如:select mySeq.NextVal,city from post

 

 

 

Connect by 语句

该语句结合伪列rownum或level 可以产生一个结果集.

1.    基本用法:

产生1~~100之间的整数

Select rownum xh from dual connect by rownum<=100;

Select level xh from dual connect by level<=100;

2.  高级用法

2.1.产生所有汉字,汉字内码为:19968~~~40869之间

  select t.* from(
    select rownum xh,nchr(rownum) hz from dual
    connect by rownum<65535
  ) t
  where t.xh between 19968 and 40869

 

2.2.查找某个汉字的内码

       使用CTE:

with myChinese as(
       select t.* from(
           select rownum xh,nchr(rownum) hz from dual
           connect by rownum<65535
       ) t
       where t.xh between 19968 and 40869
)

select * from myChinese where hz='东' –查找汉字'东'的内码

2.3.拆分字符串

with t as (select '中华人民共和国' sentence from dual)
select substr(sentence,rownum,1) from t
connect by rownum<=(select length(sentence) from t)
--order by NLSSORT(substr(sentence,rownum,1) , 'NLS_SORT=SCHINESE_STROKE_M');--按笔画排序

 

 

 

一、集合操作

UNION         由每个查询选择的所有不重复的行          并集不包含重复值
UNION ALL         由每个查询选择的所有的行,包括所有重复的行         完全并集包含重复值
INTERSECT         由每个查询选择的所有不重复的相交行          交集
MINUS         在第一个查询中,不在后面查询中,并且结果行不重复          差集

 

   所有的集合运算与等号的优先级相同,如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序,Oracle服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT (相交) 运算查询中的赋值顺序。

Union all 效率一般比union高。
1.1.union和union all
UNION(联合)运算
UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行,但除去任何重复的行。

例:

Sql代码

select  e1.empno,e1.ename,e1.mgr from emp e1 union select e2.empno,e2.job,e2.sal   
from emp e2 
select  e1.empno,e1.ename,e1.mgr from emp e1 union select e2.empno,e2.job,e2.sal

from emp e2

 

原则 :
      (1)被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。
       (2)联合运算在所有被选择的列上进行。
      (3)在做重复检查的时候不忽略空(NULL)值。
       (4)IN运算有比UNION运算高的优先级。
      (5)在默认情况下,输出以SELECT子句的第一列的升序排序。  在例子中将输出empno,ename,mgr三列数据。

全联合(UNION ALL)运算
用全联合运算从多个查询中返回所有行。
原则:
      (1)和联合不同,重复的行不被过滤,并且默认情况下输出不排序。
       不能使用DISTINCT关键字。 (2)
使用:
Select statement union | union all Select statement;

本文由金沙国际平台登录发布于金沙国际平台登录,转载请注明出处:sql的max之类的聚合函数只能针对同一列的n行运算

您可能还会对下面的文章感兴趣: