row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的).
与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码．
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．
lag（arg1,arg2,arg3):
arg1是从其他行返回的表达式
arg2是希望检索的当前行分区的偏移量。是一个正的偏移量，时一个往回检索以前的行的数目。
arg3是在arg2表示的数目超出了分组的范围时返回的值。
看几个SQL语句:
语句一:
select row_number() over(order by sale/cnt desc) as sort, sale/cnt
from (
select -60 as sale,3 as cnt from dual union
select 24 as sale,6 as cnt from dual union
select 50 as sale,5 as cnt from dual unio ......
Oracle 的rownum 原理和使用
在Oracle 中，要按特定条件查询前N条记录，用个rownum 就搞定了。
select * from emp where rownum <= 5
而且书上也告诫，不能对rownum 用">"，这也就意味着，如果你想用
select * from emp where rownum > 5
则是失败的。要知道为什么会失败，则需要了解rownum 背后的机制：
1 Oracle executes your query.
2 Oracle fetches the first row and calls it row number 1.
3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.
4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
5 Go to step 3.
了解了原理，就知道rownum >不会成功，因为在第三步的时候查询出的行已经被丢弃，第四步查出来的rownum 仍然是1,这样永远也不会成功。
同样道理，rownum 如果单独用=,也只有在rownum =1时才有用。
对于rownum 来说它是oracle 系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum 不能� ......
单表插入以insert into开头,不能有then into语句.
多表插入以insert first/all 开头,可以有then into语句
在Oracle操作过程中经常会遇到同时向多个不同的表插入数据，此时用该语句就非常合适。
All表示非短路运算，即满足了第一个条件也得向下执行查看是否满足其它条件，而First是短路运算找到合适条件就不向下进行。
INSERT ALL
WHEN prod_category=’B’ THEN
INTO book_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
WHEN prod_category=’V’ THEN
INTO video_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
WHEN prod_category=’A’ THEN
INTO audio_sales(prod_id,cust_id,qty_sold,amt_sold)
VALUES(product_id,customer_id,sale_qty,sale_price)
SELECT prod_category ,product_id ,customer_id ,sale_qty
,sale_price
from sales_detail;
Merging Rows into a Table
MERGE INTO oe.product_information pi
USING (SELECT product_id, list_price, min_price
from new_prices) NP
ON (pi.product_id = ......
数据库查询中难免会遇到行列转换的情况，摘列一些较精典的解决方案
--####################################################################
一、采用SQL decode和PL/SQL函数实现
--####################################################################
1、固定列数的行列转换
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
2、不定列行列转换
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_ ......
<!--
/* Font Definitions */
@font-face
{font-family:宋体;
panose-1:2 1 6 0 3 1 1 1 1 1;
mso-font-alt:SimSun;
mso-font-charset:134;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:3 135135232 16 0 262145 0;}
@font-face
{font-family:"\@宋体";
panose-1:2 1 6 0 3 1 1 1 1 1;
mso-font-charset:134;
mso-generic-font-family:auto;
mso-font-pitch:variable;
mso-font-signature:3 135135232 16 0 262145 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-parent:"";
margin:0cm;
margin-bottom:.0001pt;
text-align:justify;
text-justify:inter-ideograph;
mso-pagination:none;
font-size:10.5pt;
mso-bidi-font-size:12.0pt;
font-family:"Times New Roman";
mso-fareast-font-family:宋体;
mso-font-kerning:1.0pt;}
/* Page Definitions */
@page
{mso-page-border-surround-header:no;
mso-page-border-surround-footer:no;}
@page Section1
{size:595.3pt 841.9pt;
margin:72 ......
练习:
drop table Employee;
create table Employee(
id number primary key,
uName varchar2(32) ,
dept_id int ,
salary number
);
insert into Employee (id ,uName, dept_id, salary ) values (1,'ZhangShan',null, 10000);
insert into Employee (id ,uName, dept_id, salary ) values (2,'LiShi',1, 8000);
insert into&nbs ......
最新文章 :