--清楚缓存:
--每次查询前清空缓存,试试:
--10g以上:
alter system flush buffer_cache;
--9i:
ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
--建表
create table test_zhi(
test_zhi_id number,
test_zhi_name varchar2(10) default '' not null,
test_zhi_address varchar2(50) default '' not null
)
--建序列
create sequence seq_id
increment by 1 --每次加1
start with 1 --从1开始
nomaxvalue --不设最大值
nocycle --一直累加,不循环
nocache --不建缓冲区
--minvalue 1 --最小值
/* 注:一般在项目中会用缓冲区,提高效率,(格式:cache 20)
缺点,如果ORACLE死机或者其它意外情况,会出到序列不连续的情况
如果设置了最大值,一定要设置累加循环(maxvalue 10000,cycle)*/
--自动增长触发器
drop trigger tri_seq_id
create trigger tri_seq_id before
insert on test_zhi for each row when(new.test_zhi_id is null)
begin
select seq_id.nexval into :new.test_zhi_id from dual;
end;
--插入数据
insert into test_zhi values(seq_id.nextval,'姓名2abcd','地址2abcd');
select * from test_zhi;
create or replace procedure proc_test_zhi as
cursor cur_test_zhi is
select * from test_zhi;
v_name test_zhi.test_zhi_name%type;
v_address test_zhi.test_zhi_address%type;
Select * from
(
Select zhi.*, rownum rn
From (select * from test_zhi) zhi
Where rownum <= 40
)
Where rn >= 1
--(start)把行转换成列:--------------------------------------------------
DEPTNO REASON NUM
------------ ---------- ----------
LEAN A 10 8
LEAN B 10 4
LEAN B 33 1
LEAN C 41 2
LEAN C 10 3
LEAN C 11 1
LEAN D 10 2
LEAN D 11 1
--转换成:
DEPTNO REASON 10 REASON 11 REASON 33 REASON 41
------------ ---------- ---------- ---------- ----------
LEAN A 8 0 0 0
LEAN B 4 0 1 0
LEAN C 3 1 0 2
LEAN D 2 1 0 0
select deptno,
max(decode(reason, 10, num, 0)) "reason 10",
max(decode(reason, 11, num, 0)) "reason 11",
max(decode(reason, 33, num, 0)) "reason 33",
max(decode(reason, 41, num, 0)) "reason 41"
from test
group by deptno;
select name,
sum(decode(zfname,'size1',1,0)) size1,
sum(decode(zfname,'size2',1,0)) size2,
sum(decode(zfname,'size3',1,0)) size3
from test
group by name
--或:
select * from (
select times_purchased, state_code
from customers t
)
pivot -- 行转列
(
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
--(end)把行转换成列:--------------------------------------------------
--pivot语法:
select * from table pivot(
要统计的列
for 自定义列名 in('列名中的值1'as'要展示的字段1','列名中的值2'as'要展示的字段2')
)
--unpivot语法:
select * from table unpivot(
自定义列名
for 自定义列名 in(要转换的已有列名1as'要展示的字段1',要转换的已有列名2as'要展示的字段2')
)
--(start)把列转换成行:--------------------------------------------------
select *
from cust_matrix
unpivot -- 列转行
(
state_counts
for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
)
order by "Puchase Frequency", state_code
--(end)把列转换成行:--------------------------------------------------
--(start)把行转换成列(字符串类型的):--------------------------------------------------
create table test
( user_id number not null enable,
question_id number not null enable,
response varchar2(20)
);
insert into test values (123,1,'apple');
insert into test values (123,1,'banana');
insert into test values (123,1,'mango');
insert into test values (123,2,'dog');
insert into test values (124,1,'grape');
insert into test values (124,2,'cat');
insert into test values (124,2,'dolphin');
insert into test values (125,1,'orange');
insert into test values (125,3,'usa');
select * from test;
select user_id,
max(decode(question_id, 1, zhi)) Q1,
max(decode(question_id, 2, zhi)) Q2,
max(decode(question_id, 3, zhi)) Q3,
max(decode(question_id, 4, zhi)) Q4
from (select user_id, question_id, substr(max(sys_connect_by_path(response, ', ')), 2) zhi
from (select t.*, row_number() over(partition by t.user_id, t.question_id order by t.response) rn
from test t)
start with rn = 1
connect by prior user_id = user_id
and prior question_id = question_id
and prior rn = rn - 1
group by user_id, question_id)
group by user_id
order by user_id;
--(end)把行转换成列(字符串类型的):--------------------------------------------------
/*
row_number() 顺序号码, 也就是 行号, 比如 1,2,3,4,5 这样的顺序。
over() 语法需要,必须的。
partition by t.user_id, t.question_id 是按照 t.user_id, t.question_id 分区。
也就是 如果有 不同的 t.user_id, t.question_id , 这个 序号又重新从1开始计算。
order by t.response 是 排序方式, 也就是按最小的 t.response排序
row_number() 是1,然后随着 t.response 的增加, row_number() 不断递增。
*/
--每次查询前清空缓存,试试:
--10g以上:
alter system flush buffer_cache;
--9i:
ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
--建表
create table test_zhi(
test_zhi_id number,
test_zhi_name varchar2(10) default '' not null,
test_zhi_address varchar2(50) default '' not null
)
--建序列
create sequence seq_id
increment by 1 --每次加1
start with 1 --从1开始
nomaxvalue --不设最大值
nocycle --一直累加,不循环
nocache --不建缓冲区
--minvalue 1 --最小值
/* 注:一般在项目中会用缓冲区,提高效率,(格式:cache 20)
缺点,如果ORACLE死机或者其它意外情况,会出到序列不连续的情况
如果设置了最大值,一定要设置累加循环(maxvalue 10000,cycle)*/
--自动增长触发器
drop trigger tri_seq_id
create trigger tri_seq_id before
insert on test_zhi for each row when(new.test_zhi_id is null)
begin
select seq_id.nexval into :new.test_zhi_id from dual;
end;
--插入数据
insert into test_zhi values(seq_id.nextval,'姓名2abcd','地址2abcd');
select * from test_zhi;
create or replace procedure proc_test_zhi as
cursor cur_test_zhi is
select * from test_zhi;
v_name test_zhi.test_zhi_name%type;
v_address test_zhi.test_zhi_address%type;
Select * from
(
Select zhi.*, rownum rn
From (select * from test_zhi) zhi
Where rownum <= 40
)
Where rn >= 1
--(start)把行转换成列:--------------------------------------------------
DEPTNO REASON NUM
------------ ---------- ----------
LEAN A 10 8
LEAN B 10 4
LEAN B 33 1
LEAN C 41 2
LEAN C 10 3
LEAN C 11 1
LEAN D 10 2
LEAN D 11 1
--转换成:
DEPTNO REASON 10 REASON 11 REASON 33 REASON 41
------------ ---------- ---------- ---------- ----------
LEAN A 8 0 0 0
LEAN B 4 0 1 0
LEAN C 3 1 0 2
LEAN D 2 1 0 0
select deptno,
max(decode(reason, 10, num, 0)) "reason 10",
max(decode(reason, 11, num, 0)) "reason 11",
max(decode(reason, 33, num, 0)) "reason 33",
max(decode(reason, 41, num, 0)) "reason 41"
from test
group by deptno;
select name,
sum(decode(zfname,'size1',1,0)) size1,
sum(decode(zfname,'size2',1,0)) size2,
sum(decode(zfname,'size3',1,0)) size3
from test
group by name
--或:
select * from (
select times_purchased, state_code
from customers t
)
pivot -- 行转列
(
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
--(end)把行转换成列:--------------------------------------------------
--pivot语法:
select * from table pivot(
要统计的列
for 自定义列名 in('列名中的值1'as'要展示的字段1','列名中的值2'as'要展示的字段2')
)
--unpivot语法:
select * from table unpivot(
自定义列名
for 自定义列名 in(要转换的已有列名1as'要展示的字段1',要转换的已有列名2as'要展示的字段2')
)
--(start)把列转换成行:--------------------------------------------------
select *
from cust_matrix
unpivot -- 列转行
(
state_counts
for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
)
order by "Puchase Frequency", state_code
--(end)把列转换成行:--------------------------------------------------
--(start)把行转换成列(字符串类型的):--------------------------------------------------
create table test
( user_id number not null enable,
question_id number not null enable,
response varchar2(20)
);
insert into test values (123,1,'apple');
insert into test values (123,1,'banana');
insert into test values (123,1,'mango');
insert into test values (123,2,'dog');
insert into test values (124,1,'grape');
insert into test values (124,2,'cat');
insert into test values (124,2,'dolphin');
insert into test values (125,1,'orange');
insert into test values (125,3,'usa');
select * from test;
select user_id,
max(decode(question_id, 1, zhi)) Q1,
max(decode(question_id, 2, zhi)) Q2,
max(decode(question_id, 3, zhi)) Q3,
max(decode(question_id, 4, zhi)) Q4
from (select user_id, question_id, substr(max(sys_connect_by_path(response, ', ')), 2) zhi
from (select t.*, row_number() over(partition by t.user_id, t.question_id order by t.response) rn
from test t)
start with rn = 1
connect by prior user_id = user_id
and prior question_id = question_id
and prior rn = rn - 1
group by user_id, question_id)
group by user_id
order by user_id;
--(end)把行转换成列(字符串类型的):--------------------------------------------------
/*
row_number() 顺序号码, 也就是 行号, 比如 1,2,3,4,5 这样的顺序。
over() 语法需要,必须的。
partition by t.user_id, t.question_id 是按照 t.user_id, t.question_id 分区。
也就是 如果有 不同的 t.user_id, t.question_id , 这个 序号又重新从1开始计算。
order by t.response 是 排序方式, 也就是按最小的 t.response排序
row_number() 是1,然后随着 t.response 的增加, row_number() 不断递增。
*/